copy and paste

[Guest]

Hi,
Can someone please help me with the following task:
I am trying to copy a block of cells from sheet1 (starting from
Cell("B1")) and ending on the last cell (most right, most down cell, which
may vary on each execution), into sheet2.


Thanks,
Jeff

 

[Norman Jones]

Hi Jeff,

If you are defining the last cell as the last populated cell in the last
column. try:

Sub Tester()
Dim rng As Range
With ActiveWorkbook.Sheets("Sheet1")
Set rng = .Cells(1, Columns.Count).End(xlToLeft)
Set rng = Cells(Rows.Count, rng.Column).End(xlUp)
Range(.Range("B1"), rng).Copy Sheets("Sheet2").Range("A1")
End With
End Sub

You have not indicated where in Sheet2 the cells are to be copied.This will
copy the block to Sheet2 starting in cell A1. Adjust A1 (in the penultimate
line) to suit.