You can copy the data from sheet2 to sheet1 by the macro below;
Sub test()
With Sheets("Sheet2")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
..Rows("1:" & lastrow).Copy Destination:=Sheets("Sheet1").Range("A1")
End With
End Sub

What do you really want to do? Merger all 650 worksheets into one?
Do you just want to copy, or link?



"Anne" wrote:

OK, there isn't a way to combine the
=IF(Sheet2!A1="","",Sheet2!A1)
formula with something like another IF statement, where I could say ISBLANK
= TRUE and get Excel to select the rows in sheet2 that aren't blank (i.e.,
all the rows that have data in them)?

I have something like 650 worksheets to work through, so any automation on
the copy/paste from sheet2 to sheet1 would be WONDERFUL.
Anne

"Sheeloo" wrote:


You can find, by a formula, the last row in Col of Sheet2 but you still will
have to copy the formula down unless you do that through a macro...

"Anne" wrote:

Hello! Thanks!
When I do
=IF(Sheet2!A1= "","",Sheet2!A1)

that works, but then I have to manually copy the formula down however many
rows I have in sheet2 (which varies considerably, depending on the report) in
sheet1. For example, if I have 436 rows in sheet2, I have to manually copy
the formula down 435 rows in sheet1. Is there a way, using the formula above,
to check in sheet2 column A for the last row with data?

Thanks
Anne

"Sheeloo" wrote:

You can not... without using formulas.

In any Excel you have a formula or you don't...

One way is to use something like
=IF('sheet2'!a2="","",'sheet2'!a2) so that the value shows up in Sheet1 only
if it is there in Sheet2

With macro you need two steps
1. Find the last row in Sheet2
With Sheets("Sheet2")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

2. Copy and paste values/formulas from sheet2 to sheet1



"Anne" wrote:

Hello! I'd like to be able to copy data from sheet2 to sheet1. I know the
data always starts on A2 in both sheets. However, in sheet2, the row count
can be anywhere from 1 to ??. How can I do a formula that allows me to
automate the copy procedure from sheet2 to sheet1 this way?
I know the way to copy from sheet2 to sheet1 for a single row is:
in sheet1:A2, type in the formula ='sheet2'!a2
If I copy and paste special/formula from sheet1:a3 to a-whatever down the
page in sheet1, it works. But I'd like to be able to tell Excel how to adjust
for the varying numbers of rows in sheet2.
Thanks!