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!
Sub Mergesheets()
'Merge all the monthly datasheets in the workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False
Set cs = Sheets("Data")
cs.Activate
Range("A2:z" & Rows.Count).ClearContents
For Each ws In Worksheets
If ws.Name = "12011" Or ws.Name = "22011" Or ws.Name = "32011" Or ws.Name = "42011" Or ws.Name = "52011" Or ws.Name = "62011" Or ws.Name = "72011" Or ws.Name = "82011" Or ws.Name = "92011" Or ws.Name = "102011" Or ws.Name = "112011" Or ws.Name = "122011" Then
NR = cs.Range("B" & Rows.Count).End(xlUp).Row + 1
LR = ws.Range("B" & Rows.Count).End(xlUp).Row
ws.Range("A4:F" & LR).Copy cs.Range("B" & NR)
End If
Next ws
Sheets("Control").Activate
Application.ScreenUpdating = True
End Sub
Bookmarks