copy data from sheet2 to sheet1 when sheet2 has variable # of rows

[Anne]

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!

 

[Sheeloo]

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]

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]

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]

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]

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]

Hello!

We have many reports which are Excel files. Each of these reports has to be
copied into a specially formatted Excel sheet. So... I set up the specially
formatted sheet and the Excel report (from MicroStrategy) in one workbook. I
know that:

Sheet1!A1:C1 has column headers of Vendor Name, Description and Amount Paid.
Sheet2!B11 has column headers of Vendor Name, Description and Amount Paid.
Sheet2!B2 to B(whatever) has vendor names (character data).
Sheet2!C2 to C(whatever) has description data (again, character data).
Sheet2!D2 to D(whatever) has the amount paid to each vendor (as currency
data).

Sheet2 can run anywhere from 2 rows of vendor data to over 4000 rows.

So I was hoping for something like the macro you've shown me (haven't tested
it yet) to help the copying/pasting process from Sheet2 (the MicroStrategy
vendor report, exported as Excel) to Sheet1 (the formatted report I will
eventually export as a PDF file for posting to a web site).

Thanks for your help... I'll try the macro and see what happens.

Anne