VBA code to copy data from multiple worksheets into 1 consolidated worksheet

[Mam1230]

Hi, I am looking a macro to copy data from multipleworksheets to a single worksheet.

I have a spreadsheet that has 3 different worksheets. Within each worksheet thereare 3 columns that I want copied over to the main worksheet that I have createdand named Consolidated.

I also want it to copy the name of the worksheet into column A andthen paste the data next to it. That way, I will be able to see which worksheetthe data is for.


3 sheets are there Dollar Tree, Family Dollar, and BJs and 4^thsheet i.e. consolidated sheet is the final output sheet.
Row 5 have heading and data starts from row 6.

I want to copy the data from the above 3 sheets to a consolidated sheet i.e.sheet 4.

The information on the sheets changes every day with and I want the data onfrom those columns to update on the Consolidated sheet as the source sheetschange so that the consolidated sheet is always an accurate record of what’s onthe source sheets.<o></o>
I have already created a conditional formatting forcolumns E and forward to shade dates based on the dates in columns C and Dwhich will be brought over if this is successfully done.<o></o>
One thing the information represents the same informationbut is in different columns on each worksheet. On Dollar Tree its columns (P,Q,R). Family BJs its (I,J,K).<o></o>
The 2 rows I have already entered into the Consolidationsheet are an attempt to show you what I am looking for. See how the dates entered shade the dateblocks black. This will allow me to sorta crewmember and see that persons availability and schedule and where they areworking on any given day.<o></o>
There may be a better way to do what I’m trying to do soplease let me know if there is something better than a VBA code is what I want. I just asked for this because after searchingthis forum I saw someone else ask for code for a similar project.

Here is a link to the file
https://www.dropbox.com/s/zgm92si1rinft7i/PROJECTS MASTER SPREADSHEET.xlsx?dl=0
Thanks,

 

[Mam1230]

Bump,

Can anyone help?

 

[Mam1230]

Another bump. Is this not possible? Or did I do a bad job of explaining it.

Thanks

 

[hiker95]

Another bump. Is this not possible? Or did I do a bad job of explaining it.

Thanks

Mam1230,

I just saw your other reply to another post/thread that I supplied a macro for.

Let me see what I can do for you.

 

[hiker95]

Mam1230,

After several different attempts, I have not been able to solve your request.

Maybe someone else on MrExcel will be able to help you.

 

[Mam1230]

Thank you hiker95 but you’ve already nearly solved it. On the other thread you solved it for someone else and I copied that code into my worksheet and it works all I needed was to be able to have the macro clear the previous data when I ran it because as of now it just continues to duplicate the data so let’s say I have 300 rows and then I run the macro again in stead of deleting those 300 rows and updating from the source sheets now I have 600 rows which is the 300 row duplicated. I just need the macro to delete and then redo so that it’s like a refresh copy of the source sheets. I hope that makes sense.

thanks

 

[hiker95]

Thank you hiker95 but you’ve already nearly solved it. On the other thread you solved it for someone else and I copied that code into my worksheet and it works all I needed was to be able to have the macro clear the previous data when I ran it because as of now it just continues to duplicate the data so let’s say I have 300 rows and then I run the macro again in stead of deleting those 300 rows and updating from the source sheets now I have 600 rows which is the 300 row duplicated. I just need the macro to delete and then redo so that it’s like a refresh copy of the source sheets. I hope that makes sense.

thanks

Mam1230,

Can you post the current macro code that you are now using?


Please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]