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

A

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!
 
S

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
 
A

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
 
S

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...
 
A

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
 
S

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?
 
A

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!B1:D1 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top