Maxicus
05-04-2018, 01:31 AM
Good Day
I have the following piece of code to copy an entire row when a specific cell (AD of assetSheet) equals or is less than a specific date (A1 of sheet1), I would like to change that now only to copy specific cell values. cells A, B, C & M from that row on assetSheet should be copied to cells A,B,C & D in the next available row in dueSheet.
The code is as follow:
Private Sub CommandButton3_Click()
Dim assetSheet As Worksheet
Dim dueSheet As Worksheet
Dim nextRow As Long
Dim lastRow As Long
Dim thisRow As Long
' Get the sheet references
Set assetSheet = Sheet2
Set dueSheet = Sheet6
' Find the last row on the asset sheet and the next row on the due sheet
lastRow = assetSheet.Cells(assetSheet.Rows.Count, "A").End(xlUp).Row
nextRow = dueSheet.Cells(dueSheet.Rows.Count, "A").End(xlUp).Row + 1
' Look at all rows in the asset sheet
For thisRow = 1 To lastRow
' Check if column AD contains today's date
If assetSheet.Cells(thisRow, "AD").Value <= sheet1.range("A1") Then
' Copy the cells to the due sheet
assetSheet.Cells(thisRow, "A").EntireRow.Copy Destination:=dueSheet.Cells(nextRow, "A")
' Move to the next row on the due sheet
nextRow = nextRow + 1
End If
Next thisRow
End Sub
I have the following piece of code to copy an entire row when a specific cell (AD of assetSheet) equals or is less than a specific date (A1 of sheet1), I would like to change that now only to copy specific cell values. cells A, B, C & M from that row on assetSheet should be copied to cells A,B,C & D in the next available row in dueSheet.
The code is as follow:
Private Sub CommandButton3_Click()
Dim assetSheet As Worksheet
Dim dueSheet As Worksheet
Dim nextRow As Long
Dim lastRow As Long
Dim thisRow As Long
' Get the sheet references
Set assetSheet = Sheet2
Set dueSheet = Sheet6
' Find the last row on the asset sheet and the next row on the due sheet
lastRow = assetSheet.Cells(assetSheet.Rows.Count, "A").End(xlUp).Row
nextRow = dueSheet.Cells(dueSheet.Rows.Count, "A").End(xlUp).Row + 1
' Look at all rows in the asset sheet
For thisRow = 1 To lastRow
' Check if column AD contains today's date
If assetSheet.Cells(thisRow, "AD").Value <= sheet1.range("A1") Then
' Copy the cells to the due sheet
assetSheet.Cells(thisRow, "A").EntireRow.Copy Destination:=dueSheet.Cells(nextRow, "A")
' Move to the next row on the due sheet
nextRow = nextRow + 1
End If
Next thisRow
End Sub