In the code below, I want the rows that need to be copied to the destination history…
- 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 = TrueEnd Sub
- 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 IfThis code uses the
Findmethod to locate the first empty cell in column I. If an empty cell is found, it calculates thelastRowvariable accordingly. Then, the range from B6 to thelastRowin 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 changelastRow = firstEmptyCell.Row - 1tolastRow = firstEmptyCell.Row - 2instead.Also, make sure to adjust the rest of your code accordingly to account for the potential change in the
lastRowvariable.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.