Setting Up and Creating a Macro to copy data from multiple tabs onto one main tab

Hi,

I have some excel spreadsheets that have lots of tabs. I would like to copy 2 columns from each tab so that the data is on one tab. The Columns are D & F.

I know I have to insert a Macro but I need to know what the code would be and also how to actually do this in excel, e.g how to insert the Macro. I have done this before, following some insrtuctions from another thread on here but I can't remember how to do and can't find the original thread!

Thanks.

Carrie,
It would be easier if you could attach sample book. We can then attach the macro(code) in to your sheet. Please remove any confidential info, just a sample, not the entire data.

Hi,

Please find a Sample file below. The only thing that is the same as my spreadsheet is the header. I hope this is ok. Thanks.

Sample.xls

Carrie,
Okay!

"I would like to copy 2 columns from each tab so that the data is on one tab. The Columns are D & F."
You have for instance "Cheshire" in column "D" and "Carrie" in column "E". How do you want to combine these two tabs
in to one? Do you want a comma, period & to use as joiner. Please show me in example. You have dozens of sheets in the workbook. Do you wish to combine all in a single summary sheet?

I've attached the sample sheet which now includes a Master Tab. Ideally I would just like two columns, which has all of the information from Columns D & F from all of the tabs. I hope this makes sense!
Sample.xls

Carrie,
I'm new around here, and I'm not 100% sure this code will work in Excel 2007 the same way it will in 2010, but it should be pretty similar.

Please Login or Register  to view this content.

Here's hoping that I put that together in a way that formats decently here.

Thanks Jason.

This may seen like a really stupid question, but can you also supply instructions on how to add this code onto my spreadsheet, literally step by step?

Thanks.

Originally Posted by Carrie1990

Thanks Jason.

This may seen like a really stupid question, but can you also supply instructions on how to add this code onto my spreadsheet, literally step by step?

Thanks.

Not stupid at all, Carrie. To work with the code that either AB33 or myself included (and AB's might well work better for you), you'll need to make sure that your worksheet is Macro Enabled (yours is).

Then you either go to the Developer tab in your ribbon and click on Visual Basic, or you hit Alt-F11 (I prefer the keyboard shortcuts, myself). If you don't have the Developer tab, go ahead and add it. Even if you prefer your keyboard shortcuts like I do, it helps to have the ribbon tab.

Then, under the default VisualBasicProject that you'll see on the left-hand of your screen, you should find a part of the tree labeled "Modules."

Right-click, scroll down to "Insert" -> Module

Copy/Paste the code into the blank coding window that will appear.

You can then add a custom button to your Quick Access Toolbar that will activate the macro; if you need instructions for that, please let me know!

Also let me know if you run into any problems trying to use my code, and I'll try to figure out what I've screwed up in the course of putting it together.

Have a great day!

Carrie,
As per Jason's instruction(Thanks Jason by the way).
You could also use Alt+F8 while you are in the excel page- Then choose Run macro

This has worked perfectly, thanks for all your help!