Code to copy data from multiple sheets into one final output sheet.

**kenadams378** · 08-02-2012, 05:22 AM

Morning,

I need to copy some data from 4 tab sheets into one final sheet as a final part of my macro;

Sheet names are;

Original Data
A200
A210
A220

I need the used range of these sheets to be copied and pasted together on the 'Final Output' tab.

Any ideas on what my code would be?

**arlu1201** · 08-02-2012, 06:14 AM

Will the Final Output tab already have the headers in it?

If you run the macro once and want to run it again, should the output from the 1st run be cleared and fresh data input?

Do you need the sheetname from where the data was copied to be shown in a column of the Final Output sheet?

**kenadams378** · 08-02-2012, 06:16 AM

No the headers won't be there, yes it would be good once all the data has been copied to the Final Ouput sheet, the other sheets are cleared ready to run another set of data./..

**arlu1201** · 08-02-2012, 06:18 AM

Ok, the other sheets can be cleared.

But if you have run the macro one time and the data has been consolidated into the Final Output sheet, then you realise that the data has some error (just an example) and you need to run the macro again. When you run the macro the 2nd time, should the data that is already present in the Final Output sheet be cleared and then the data consolidated again?

**kenadams378** · 08-02-2012, 06:25 AM

Yes that would be great...

**arlu1201** · 08-02-2012, 06:26 AM

So to confirm, the macro will delete the contents of the Final Output sheet and refill with data.

Once the data has been consolidated, it will clear the contents of the 4 source sheets as well.

**kenadams378** · 08-02-2012, 06:28 AM

Yes once the macro has run, consolodated the data across Original Data, A200, A220, A240, it will copy the data from these 4 tabs and populate the Final Output sheet and clear down the other 4 tabs.

It maybe worth while adding something at the start of the macro to clear the 5 tabs prior to running....

**arlu1201** · 08-02-2012, 06:52 AM

It maybe worth while adding something at the start of the macro to clear the 5 tabs prior to running....

If the source sheets are cleared prior to running the macro, how would the macro consolidate it?

Wouldnt it be better to clear the source files after running the macro and the final output file before?

**kenadams378** · 08-02-2012, 06:53 AM

I meant, it could be worthwhile for the macro to clear the sheets prior to requesting the file, in case there is data there from a previous run.

**arlu1201** · 08-02-2012, 07:08 AM

So you would like to merge the consolidation code with the code from post 72 of this thread - http://www.excelforum.com/excel-prog...-values-5.html ?

So do you want to consolidate the data after opening the file as per the macro?

---------- Post added at 04:38 PM ---------- Previous post was at 04:33 PM ----------

This is the code to consolidate.

Please Login or Register  to view this content.

This code clears the contents of the Final Output sheet before running the macro. Once you explain the other stuff, i can help you change it to what you need.

**kenadams378** · 08-02-2012, 07:56 AM

Does the code you have given above copy all data from the 4 tabs and paste it into the final output?

**arlu1201** · 08-02-2012, 07:58 AM

Yes, try it and let me know what needs to be changed.

I have put some comments after the code.

**kenadams378** · 08-02-2012, 08:13 AM

Doesn't like this bit

Please Login or Register  to view this content.

Not sure where the mysheet is!!!!

**kenadams378** · 08-02-2012, 08:42 AM

this is now my full code;

Please Login or Register  to view this content.

**arlu1201** · 08-02-2012, 08:53 AM

Mysheet is nothing but an array. So first, it will consolidate the data from original data, then from the other 3 sheets.

Change these lines from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Also, put this bit of code before this line

Please Login or Register  to view this content.

. Remove it from where you have currently put it.

**kenadams378** · 08-02-2012, 08:57 AM

This better;

Please Login or Register  to view this content.

**arlu1201** · 08-02-2012, 09:00 AM

As per the change i suggested, this is the final code that you can use -

Please Login or Register  to view this content.

**kenadams378** · 08-02-2012, 09:03 AM

Doesnt like this bit of this line;

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Error message is - Sub or Function not defined

**arlu1201** · 08-02-2012, 09:04 AM

Oops...just add a "." in between.

It has to be Thisworkbook.Worksheets

**kenadams378** · 08-02-2012, 09:11 AM

Works a treat, one other question, i have this code;

Please Login or Register  to view this content.

but i still get a pop up about links to other data sources, how do i stop this using code?

**arlu1201** · 08-02-2012, 09:29 AM

I dont think you can stop that.

**arlu1201** · 08-03-2012, 08:51 AM

Is this thread solved?

Code to copy data from multiple sheets into one final output sheet.

LinkBack

Thread Tools

Rate This Thread

Display

Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Re: Code to copy data from multiple sheets into one final output sheet.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions