Add data columns and merge specific data sheets into master sheet

Hi,

I have a workbook with multiple sheets in it. Some contain raw data and some contain calculations etc. Every month I add a new data sheet that's in the same format as the other data sheets - so i have a sequence of sheets named 12011, 22011, 32011...122011, 12012, 22012, etc.

I would like help writing 2 pieces of code:

The first piece of code should add the title "Month" in cell E3, and add the 'number' of that month in the rest of column E.
So for July, cells E4 to wherever the last record is (they will differ each month), should all contain "7".
Similarly in cell F3, there should be the title "Year", and the year should be inserted in the rest of the column.
If possible the month and year should be extracted from the sheet names as detailed above, and the code should run for all the sheets with the month-year naming convention.

The second piece of code will then select all the data in the monthly data sheets and compile into a master sheet.
I currently have a piece of code that does something similar, but everytime I add a new sheet, I have to add the sheet name to the code (code attached below).
(And I only want to copy the month-year named data sheets into the master sheet).

sorry for the loooong winded question, but any help would be greatly appreciated!

Please Login or Register  to view this content.

Maybe one way?

Please Login or Register  to view this content.

Originally Posted by sshahils

... but everytime I add a new sheet, I have to add the sheet name to the code

hi, sshahils, try to change this code block:

Please Login or Register  to view this content.

to this one:

Please Login or Register  to view this content.