Results 1 to 5 of 5

Add data columns and merge specific data sheets into master sheet

Threaded View

  1. #1
    Registered User
    Join Date
    07-15-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    6

    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!

    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
    Last edited by sshahils; 07-15-2011 at 10:25 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1