I've edit my original post and given you more details. Basically I have this code:
For x = 1 To 1000
If track.Cells(x, 1) = Date Then
If track.Cells(x, 2) = "EU" Then
If track.Cells(x, 3) = "ABCDE" Then
ST.Range("ABCDE").Copy
EU.Cells(41, 2).PasteSpecial
Call ActivateSheet
track.Range(Cells(x, 4), Cells(x, 9)).Copy
**For i = 42 To 1000
If EU.Cells(42 + i, 2) Then
EU.Cells(42 + 1, 2).PasteSpecial
End If
Next i**
End If
End If
End If Next x
And I'm having problems with this specific part of the code:
For i = 42 To 1000
If EU.Cells(42 + i, 2) Then
EU.Cells(42 + 1, 2).PasteSpecial
End If
Next i
What this basically does is copy a range of cells from another worksheet on the same workbook and it should place it on another worksheet. Now, what happens with the code i'm having problems is, I want it to paste to the row below AFTER it pastes the first one. Are there any other ways I can achieve this? I'm only starting to self teach myself in VBA coding. Any advice would be greatly appreciated.
You don't need to loop the values to find the bottom row, you can simply come from the bottom up in one go.
replace this:
With this:
How it works:
Cells(rows.count,2) is the very last row in the sheet and column 2 (B1048576 nowadays or B65536 in the old days)
.End(XLup) tells it to go up until data, exactly like CTRL-UP in Excel
This tells us the last row that contains data, we don't want to paste here because you will overwrite your last row so:
.offset(1,0) tells it to go ONE row and zero columns away from where it is.
This has worked out the address of where you need to paste, then we pastespecial.
Hope that helps
To actually see how this works go to the immediate window in the VB editor using CTRL-G and paste the following code:
Hit enter after each line and you will see how each added portion of code to the line alters the results