Hi All,
I have managed somehow to write some VBA code which does most of what I want but unfortunately only the last value from column 35 is being picked up and placed against the first item and I can't see why.
The spreadsheet has 38 columns and I want to:
1. Press a button and have it look up column 35 and find all cells greater than zero.
2. If the cell value in column 35 is greater than zero then copy across the respective rows in columns 1 to 4 and 35 to a second sheet and place them in columns 1 to 4 and column 10.
3. If the value in column 35 is equal to zero do not copy across any data from that row.
Thanks in advance.
Here is the code:
I have managed somehow to write some VBA code which does most of what I want but unfortunately only the last value from column 35 is being picked up and placed against the first item and I can't see why.
The spreadsheet has 38 columns and I want to:
1. Press a button and have it look up column 35 and find all cells greater than zero.
2. If the cell value in column 35 is greater than zero then copy across the respective rows in columns 1 to 4 and 35 to a second sheet and place them in columns 1 to 4 and column 10.
3. If the value in column 35 is equal to zero do not copy across any data from that row.
Thanks in advance.
Here is the code:
Code:
Private Sub CommandButton1_Click()
'Find last row
Dim LastRow As Long, erow As Long
'To check the last row of data on sheet
LastRow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To LastRow '5=row five
If Sheet4.Cells(i, 35).Value <> 0 Then 'Value to be looked up - change to >0
Sheet4.Cells(i, 1).Copy 'Copy column one
erow = Sheet3.Cells(Rows.Count, 5).End(xlUp).Row 'Second worksheet
Sheet4.Paste Destination:=Sheet1.Cells(erow + 1, 1) 'Copy and paste in column "A"
Sheet4.Cells(i, 2).Copy 'Copy column two
Sheet4.Paste Destination:=Sheet1.Cells(erow + 1, 2) 'Copy and paste in column "B"
Sheet4.Cells(i, 3).Copy 'Copy column three
Sheet4.Paste Destination:=Sheet1.Cells(erow + 1, 3) 'Copy and paste in column "C"
Sheet4.Cells(i, 4).Copy 'Copy column four
Sheet4.Paste Destination:=Sheet1.Cells(erow + 1, 4) 'Copy and paste in column "D"
[B][COLOR=#ff0000]'This part isn't working correctly and is only picking up the very last value in the column[/COLOR][/B]
Sheet4.Cells(i, 35).Copy 'Copy column Closing Stock
Sheet4.Paste Destination:=Sheet1.Cells(erow + 1, 10) 'Copy and paste column Closing Stock
End If
Next i
Range("B2").Select 'Finish by selecting this cell
End Sub