Copy selected cells from a range of worksheets and paste in master table

**simba3088** · 08-16-2012, 04:45 AM

Trying to write a program that will copy cells V1, AB3, AB4 AB5 and AF4 from a worksheet and paste them in single row in a master table, then repeat the process on the next worksheet and put the data in the next row of the master table, all sheets are in one workbook.

The range of worksheets I want to copy from will be extended indefinitely, but they come from a template so the info I want will always be in the same cells.

The workbook also has a coversheet as well as a number of other charts etc that I don't want to copy these cells from, but these won't be added to or changed, so ideally I trying to write some kind of loop function that can go through the whole workbook, but ignore some sheets that I am able to name in the code.

Cheers

**arlu1201** · 08-16-2012, 04:50 AM

Sure, this can be done.

Will the master sheet be ready or should the macro create it? If it already exists, what's its name?

Which are the sheets to be excluded? If i provide a few sample sheet names in the code, will you be able to edit them according to your workbook?

**simba3088** · 08-16-2012, 05:07 AM

Thanks for the quick reply!

The sheets that are to be excluded are named: "Overview" "Ratings Guide" "Template" "Blank" "Master Table" "Basket 1" "Basket 2" and "Basket 3"

Sheet "Master Table" is where I want to put the information.

The order I want to put the cells in the table is V1, AB4, AF4, AB3, AB5

to go in columns A, B, C, D and E respectively on the "Master Table" sheet.

In the final version of this workbook I want to hide the sheet called "Master Table" but copy the entire table onto the sheets called "Basket 1" "Basket 2" and "Basket 3" which will have tables with filters on them.

The idea behind the macro is that every time a new sheet is added or an old one amended the information can be automatically put in the master, but actually be displayed in the "basket" sheet where it meets the filtering criteria.

Hope that makes sense....

cheers again

**arlu1201** · 08-16-2012, 06:53 AM

Couple of questions -

In the final version of this workbook I want to hide the sheet called "Master Table" but copy the entire table onto the sheets called "Basket 1" "Basket 2" and "Basket 3" which will have tables with filters on them.

Will these Basket sheets have data already in them?

Should the data be duplicated into these 3 sheets? What about the filters? Will you be applying them yourself after the macro runs or do you want the macro to put them in for you?

The idea behind the macro is that every time a new sheet is added or an old one amended the information can be automatically put in the master, but actually be displayed in the "basket" sheet where it meets the filtering criteria.

Do you want the master sheet to be cleared before putting in fresh data each time you run the macro?

**simba3088** · 08-16-2012, 07:27 AM

The answer to all those questions is yes.

If the master table can be cleared everytime and re filled with all new information that would be great (but maybe make the code quite slow?)
The baskets (like the master table) could also be empied and all the values from the master sheet duplicated into each one, that would also be great.

If we can include the filters to be applied to the baskets in the code that would be fantastic, the only problem here is that those might change. i.e at the moment I might want basket 1 to 'show all the entrys in that have value a greater than 5 in column A', but that might change to 'show all the entrys with values greater than 7 in column A and values less than 2 in column B'....so it might be best to leave that to be applied manually each time unless there is a way to for the code to chose its filter values from a range cells on each baskets sheet that could be changed. Something like

set column filter for (Coloumn A, Sheet "Basket !") to be greater than value in Range ("G1") of sheet("basket 1")

and then have a table on each sheet where the values can be added....

thanks again

**arlu1201** · 08-20-2012, 07:59 AM

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Regarding the filters to be applied to Basket 1, 2 and 3, you will need to specify the filters for each. The code will copy the data into the 3 sheets but the filters will not take into account the new data that has been added. The filters will need to be applied again.