how select block w/unknown last col and last row?
 

Ian,

Try this:
In the copied from sheet, without inserting blank lines:
Range("A1").select
Range(Selection, Selection.end(xlToRight)).Select
Range(Selection, Selection.end(xlDown)).Select
Selection.Copy

In the copied to sheet:
Range("A4").Select
Selection.PasteSpecial....

This way you will only copy the populated range.
Important: no gaps in row 1 and Column A!

If you have gaps in row 1 but not in column A, you could
alternatively copy entire rows:
Rows("1").Select
Range(Selection, Selection.end(xlDown)).Select
Selection.Copy

Caution: If it may be that the range you are pasting is
narrower / shorter than the previous one, you will need to
clear the old data first. You can do so by selecting in
the same manner and deleting / clearing contents.

Nikos


-----Original Message-----
Thanks in advance.
I have a macro that copies a worksheet from one workbook
to another. In the workbook copied to, the first row has
headers for about 20-30 columns, and then data below. But
the workbook copied from does not, it just has the data.
The way I copy the info now is:
Rows("1:3").Select
Selection.Insert Shift:=xlDown 'insert three rows at top
Range("A4:IV65536").Select
Selection.Copy
ThisWorkbook.Worksheets("B Records").Activate
Range("A4:IV65536").Select
Selection.PasteSpecial
This is the way I copy it so the headers in the first row
of the workbook copied to do not get overwritten.
The problem is Selection.PasteSpecial takes a few
seconds.
I figure there is a more graceful way of doing this,
something like using:
Range("A4:" & ActiveSheet.Range("A65536").End
(xlUp).Address).Select or something like that (this
doesn't work of course).
The number of rows of data will change from time to time,
and also the columns might too. So I can't just do a Range
("A1:Y20").Select.
One way I think this might be possible is do a
Cells.Select to select the whole sheet and then deselect
the first three rows, then paste into there. Is this
possible? Or any other ideas? Thanks again.
.