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

In the code below, I want the rows that need to be copied to the destination history…

  1. User

    In the code below, I want the rows that need to be copied to the destination history sheet to be only be from the source sheet B6 to column L and only the rows above the first empty cell in column I. The condition 'historyWS.Range(“B” & pasteRow & “:L” & (pasteRow + lastRow - 6)).PasteSpecial xlPasteValues' within the code below does not do this.

    'historyWS.Range(“B” & pasteRow & “:L” & (pasteRow + lastRow - 6)).PasteSpecial xlPasteValues'

    Public Sub ServiceProvidersHistory() 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 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
    
    ' Paste the copied range into the history sheet
    historyWS.Range("B" & pasteRow & ":L" & (pasteRow + lastRow - 6)).PasteSpecial xlPasteValues
    
    ' 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

    To copy only the rows from sheet B6 to column L, and only up to the first empty cell in column I, you can modify the code as follows:

    ' 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 - 1
    
        ' Copy the range from B6 to the last row in L
        sourceWS.Range("B6:L" & lastRow).Copy
    
        ' Rest of the code...
        ' (Note: The condition 'historyWS.Range("B" & pasteRow & ":L" & (pasteRow + lastRow - 6)).PasteSpecial xlPasteValues' should work correctly now)
    End If
    

    This code uses the Find method to locate the first empty cell in column I. If an empty cell is found, it calculates the lastRow variable accordingly. Then, the range from B6 to the lastRow in column L is copied as before.

    Please note that in the line sourceWS.Range("B6:L" & lastRow).Copy, I assume that you want to include the row with the first empty cell in column I. If you want to exclude it, you can change lastRow = firstEmptyCell.Row - 1 to lastRow = firstEmptyCell.Row - 2 instead.

    Also, make sure to adjust the rest of your code accordingly to account for the potential change in the lastRow variable.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.