Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

In the VBA code below, the source data from the sheet in workbook "Service Providers"…

  1. User

    In the VBA code below, the source data from the sheet in workbook "Service Providers" was not copied to the destination sheet in workbook "Service Providers History"

    Public Sub ServiceProvidersHistory()

    Application.EnableEvents = False
    
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim historyWB As Workbook
    Dim historyWS As Worksheet
    Dim lastRow As Long
    Dim pasteRow As Long
    
    ' Set source workbook and worksheet
    Set sourceWB = ActiveWorkbook
    Set sourceWS = sourceWB.ActiveSheet
    
    ' Find the last row in column I
    lastRow = sourceWS.Cells(sourceWS.Rows.count, "I").End(xlUp).Row
    
    ' Copy the range from B6 to the last row in L
    sourceWS.Range("B6:L" & lastRow).Copy
    
    ' Open the history workbook
    Set historyWB = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsm")
    Application.Wait (Now + TimeValue("0:00:15"))
    ' Select the sheet with the same name as the active sheet in the source workbook
    On Error Resume Next
    Set historyWS = historyWB.Sheets(sourceWS.Name)
    On Error GoTo 0
    
    ' If the sheet doesn't exist, create a new sheet with the same name
    If historyWS Is Nothing Then
        sourceWS.Copy After:=historyWB.Sheets(historyWB.Sheets.count)
        Set historyWS = historyWB.ActiveSheet
        historyWS.Name = sourceWS.Name
        
        ' Display a message box for manual code copying
        MsgBox "Contractor sheet was not found in History but has been created. Remember to manually copy required VBA codes across."
    End If
    
     ' Activate the source workbook
    'sourceWB.Activate
    

    ' Find the first empty cell in column I Dim firstEmptyCell As Range Set firstEmptyCell = sourceWS.Range("I:I").Find("", LookIn:=xlValues, LookAt:=xlWhole)

    ' Check if an empty cell is found If Not firstEmptyCell Is Nothing Then ' Calculate the last row to copy based on the first empty cell in column I lastRow = firstEmptyCell.Row - 2 End If

    ' Copy the range from B6 to the last row in L sourceWS.Range("B6:L" & lastRow).Copy

    ' Activate the history workbook, select the history sheet
    historyWB.Activate
    historyWB.Sheets(sourceWS.Name).Select
    
    ' Find the next empty row in the history sheet
    pasteRow = historyWS.Cells(historyWS.Rows.count, "B").End(xlUp).Row + 1
    
    
    
    ' Save and close the history workbook
    historyWB.Save
    historyWB.Close
    
    ' Clear the contents from B6 to the last row in L
    sourceWS.Range("B6:L" & lastRow).ClearContents
    
    ' Move the rows below the cleared contents up to row 6
    sourceWS.Range("B7:L" & lastRow).Cut
    sourceWS.Range("B6").Insert Shift:=xlDown
    
    ' Insert today's date in C5 to I5
    Dim currentDate As Date
    currentDate = Date
    sourceWS.Range("C5:I5").Value = currentDate
    
    ' Display a message box
    MsgBox "Completed tasks have been backed up to History"
    
    ' Enable all events
    Application.EnableEvents = True
    

    End Sub

  2. Assistant

    The VBA code above copies data from the active sheet in the workbook "Service Providers" to the corresponding sheet in the workbook "Service Providers History".

    The code first disables events to prevent any triggers or events from running during the code execution. It then declares variables for the source workbook, source worksheet, history workbook, history worksheet, the last row of data in column I, and the row where the copied data will be pasted.

    The source workbook and worksheet are set to the active workbook and active sheet, respectively.

    The code finds the last row of data in column I of the source worksheet using the "End(xlUp)" method. It then copies the range of data from cell B6 to the last row in column L.

    Next, it opens the history workbook specified in the file path "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsm" using the "Workbooks.Open" method. There is a delay of 15 seconds before proceeding further using the "Application.Wait" method.

    The code attempts to select the sheet in the history workbook with the same name as the active sheet in the source workbook using the "Sheets" collection. If the sheet does not exist, it creates a new sheet after the last sheet in the history workbook, sets it as the active sheet, and names it with the same name as the source sheet. By default, it also displays a message box to remind the user to manually copy any required VBA codes to the newly created sheet.

    The code then finds the first empty cell in column I of the source worksheet using the "Find" method and assigns it to the variable "firstEmptyCell". If an empty cell is found, it calculates the last row to copy based on the row of the first empty cell.

    It copies the range of data from B6 to the calculated last row in column L of the source worksheet.

    Next, it activates the history workbook, selects the corresponding history sheet, and finds the next empty row in column B. This empty row will be the row where the copied data will be pasted.

    After that, it saves and closes the history workbook.

    The code clears the contents of the range from B6 to the calculated last row in column L of the source worksheet.

    It then moves the rows below the cleared contents up to row 6. This is done using the "Cut" method to cut the range B7 to the calculated last row in column L, and then inserting it at cell B6 with the "Insert" method.

    Lastly, it inserts today's date in cells C5 to I5 of the source worksheet, displays a message box to indicate the completion of the task, and enables all events again.

    Overall, this code allows for the backup of completed tasks in the "Service Providers" worksheet to the corresponding sheet in the "Service Providers History" workbook.