Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 27th 09, 05:36 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 74
Default copy data from sheet2 to sheet1 when sheet2 has variable # of rows

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!

  #2   Report Post  
Old February 27th 09, 07:26 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default copy data from sheet2 to sheet1 when sheet2 has variable # of rows

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!

  #3   Report Post  
Old February 27th 09, 07:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 74
Default copy data from sheet2 to sheet1 when sheet2 has variable # of

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!

  #4   Report Post  
Old February 27th 09, 07:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default copy data from sheet2 to sheet1 when sheet2 has variable # of


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!

  #5   Report Post  
Old February 27th 09, 08:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 74
Default copy data from sheet2 to sheet1 when sheet2 has variable # of

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!



  #6   Report Post  
Old February 27th 09, 09:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default copy data from sheet2 to sheet1 when sheet2 has variable # of

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!

  #7   Report Post  
Old February 27th 09, 10:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 74
Default copy data from sheet2 to sheet1 when sheet2 has variable # of

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



"Sheeloo" wrote:

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!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to Copy data from a list in sheet1 and paste into sheet2 Michael Excel Discussion (Misc queries) 3 April 23rd 08 06:52 PM
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ Dany Excel Discussion (Misc queries) 5 April 16th 07 03:27 AM
Copy result from sheet1 to sheet2 Winnie Excel Discussion (Misc queries) 3 June 26th 06 09:22 AM
Display Rows From Sheet1 In Sheet2 (Import) Mythran Excel Worksheet Functions 1 March 24th 06 08:40 PM
Copy values from Sheet1 to Sheet2 Eintsein_mc2 Excel Discussion (Misc queries) 1 January 6th 05 06:02 AM


All times are GMT +1. The time now is 10:40 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017