If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Main Form - copy exact data from multiple worksheets with live lin
the not so easy way to do this would be to create a union query based off of
the other sheets. in excel 03: dataImport external datanew 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! |
Thread Tools | |
Display Modes | |
|
|