Main Form - copy exact data from multiple worksheets with live lin

Hello,
I've been searching through the forums, but haven't found anything yet.   
Does anyone know of a way to copy data from multiple worksheets (i.e. 30) to 
one master form?  I do NOT want to summarize the data, I want to replicate it 
exactly, and have a live link so that if the individual sheets are changed, 
the master will update.  All column headings are identical, but there are 
variable numbers of rows.

My only thoughts so far are simply typing =Sheet1!A2 and dragging, repeating 
for each sheet (or I guess writing a macro to do this).  However, if I insert 
a new row on any of the sheets, it does not update.  Any way to do this also?

Thanks in advance!
0
Utf
3/15/2010 10:49:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
653 Views

Similar Articles

[PageSpeed] 48

the not so easy way to do this would be to create a union query based off of 
the other sheets. in excel 03: data>Import external data>new database query, 
select the Excel Files* option and click ok, find the work book with all the 
sheets you'd like to put together and click ok (even if it's the one you have 
open), select the first sheet use the > button and click next thrice, select 
the option that says View data or edit query in Microsoft Query. Now for the 
hard part: click the SQL button, select all the text that you see and use 
CTRL+C to copy the selection, unselect everything, after the very last letter 
in the text type a space then the word UNION capitalized then type another 
space, now use CTRL+V to paste what you had copied earlier, now in the text 
that you have pasted you should recognize the seet name of the sheet that you 
started with, change all the seet names after the word UNION to the sheet 
name of the seccond sheet, Repeat the UNION Paste UNION Paste, changing the 
sheet names to the 30 sheets..., click OK, (FYI you might want to give VBA a 
try to help you make this query), if all went right it will only complain at 
you once to which you should say OK, other wise something was misstyped..., 
close the query window, click on the first cell where you'd like the data to 
start, click OK, as long as you have not over exceeded the 256*256=65536 row 
limit it should work... 

if all you are looking for is to be able to reference this data you may want 
to look into creating a named range over these sheets...

"uncreative" wrote:

> Hello,
> I've been searching through the forums, but haven't found anything yet.   
> Does anyone know of a way to copy data from multiple worksheets (i.e. 30) to 
> one master form?  I do NOT want to summarize the data, I want to replicate it 
> exactly, and have a live link so that if the individual sheets are changed, 
> the master will update.  All column headings are identical, but there are 
> variable numbers of rows.
> 
> My only thoughts so far are simply typing =Sheet1!A2 and dragging, repeating 
> for each sheet (or I guess writing a macro to do this).  However, if I insert 
> a new row on any of the sheets, it does not update.  Any way to do this also?
> 
> Thanks in advance!
0
Utf
3/17/2010 1:43:02 AM
Reply:

Similar Artilces: