PDA

View Full Version : [SOLVED] Changing code from copy entire row to copy specific cells



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

Fluff
05-04-2018, 05:56 AM
Try
Intersect(assetsheet.Rows(thisRow), assetsheet.Range("A:C,M:M")).Copy Destination:=dueSheet.Cells(nextrow, "A")

mana
05-04-2018, 11:14 PM
Sub test()

With Sheet2.Range("A1").CurrentRegion
.AutoFilter
.AutoFilter 30, "<=" & Sheet1.Range("A1").Value2
If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
Intersect(.Offset(1), .Range("A:C,M:M")).Copy _
Sheet6.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
.AutoFilter
End With

End Sub