Prev: Is there a way to use a multiple column array in SUMPRODUCT?
Next: rename worksheet tabs
From: uncreative on 15 Mar 2010 18:49
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!
From: IdiotZ42 on 16 Mar 2010 21:43
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!
 | 
Pages: 1
Prev: Is there a way to use a multiple column array in SUMPRODUCT?
Next: rename worksheet tabs