PDA

View Full Version : paste cell offset to right???



Dibbley247
01-16-2013, 02:37 PM
I'm trying to copy a figure weekly via macro and paste it starting from B2 then to next avaliable space to the right (this will be 52 weeks so will be B2-BA2 in total)
The code I have works perfectly for counting down, but changed it and it only pastes the one time?

Where am I going wrong???


Worksheets("End of week summary").Range("E4").Copy
Worksheets("Sheet1").Range("B2" & Columns.Count).End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues

mancubus
01-16-2013, 05:00 PM
hi.
try:


Worksheets("Sheet1").Range("B" & Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteValues

Teeroy
01-18-2013, 04:37 AM
With pastespecial I've found it necessary to activate the destination sheet, even when its location is fully referenced. If you are only copying one cell value (derived from formula) and using pastespecial then it may be simpler to simply assign it. e.g.

Worksheets("Sheet1").Range("B" & Columns.Count).End(xlToLeft).Offset(0, 1) = Worksheets("End of week summary").Range("E4")