Combining several Worksheets with same headers into one worksheet

[dcincala]

I cannot figure out why my macro will not run so that all my master worksheet shows ALL columns. When it runs I only receive the first column so I am halfway there. Not sure what I need to change so that all the columns 15 columns.

Here is the macro:

Sub CopyFromWorksheets()
    Dim wrk As Workbook 'Workbook object - Always good to work with object variables
    Dim sht As Worksheet 'Object for handling worksheets in loop
    Dim trg As Worksheet 'Master Worksheet
    Dim Rng As Range 'Range object
    Dim colCount As Integer 'Column count in tables in the worksheets
     
    Set wrk = ActiveWorkbook 'Working in active workbook
     
    For Each sht In wrk.Worksheets
        If sht.Name = "Master" Then
            MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
            "Please remove or rename this worksheet since 'Master' would be" & _
            "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
            Exit Sub
        End If
    Next sht
     
     'We don't want screen updating
    Application.ScreenUpdating = False
     
     'Add new worksheet as the last worksheet
    Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
     'Rename the new worksheet
    trg.Name = "Master"
     'Get column headers from the first worksheet
     'Column count first
    Set sht = wrk.Worksheets(1)
    colCount = sht.Cells(1, 255).End(xlToLeft).Column
     'Now retrieve headers, no copy&paste needed
    With trg.Cells(1, 1).Resize(1, colCount)
        .Value = sht.Cells(1, 1).Resize(1, colCount).Value
         'Set font as bold
        .Font.Bold = True
    End With
     
     'We can start loop
    For Each sht In wrk.Worksheets
         'If worksheet in loop is the last one, stop execution (it is Master worksheet)
        If sht.Index = wrk.Worksheets.Count Then
            Exit For
        End If
         'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
        Set Rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
         'Put data into the Master worksheet
        trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
    Next sht
     'Fit the columns in Master worksheet
    trg.Columns.AutoFit
     
     'Screen updating should be activated
    Application.ScreenUpdating = True
End Sub

Thank you in advance for any help. I am not sure what to do

[JBeaucaire]

I have a macro that may be "almost ready to use" for merging data from multiple sheets into a "consolidation" sheet.

Sheets to One Sheets - Part 1

You can compare the techniques shown to your own macro.

[dcincala]

The problem is I do not know how to update to my worksheet information. I have 12 worksheets and each worksheet contains 15 columns (same for each worksheet) each WS contains a different amount of rows though.

I am trying to combine the info to one master sheet.

[JBeaucaire]

The macro already does the work for you to collect the varying rows of data into a single sheet.

Look at the sample workbook on that page so you can see it in action and compare that to your setup. If it's similar enough, just try it on your workbook. The only stuff that should need editing is the code colored to catch your attention on the web page.

[dcincala]

I am sorry, I did try it and it did not work. I just am not sure what "code" info is. I can guess at the one in red "INvoice#" since that seems like a page but the other stuff is like chinese to me:

            'customize this section to copy what you need
            If NR = 1 Then      'copy titles and data to start the consolidation
                ws.Range("A1", ws.Cells(1, Columns.Count).End(xlToLeft)).Copy
                If sName Then
                    cs.Range("B1").PasteSpecial xlPasteAll
                Else
                    cs.Range("A1").PasteSpecial xlPasteAll
                End If
                NR = 2
            End If
            
            ws.Range("A2:BB" & LR).Copy     'copy data

Do you someehow click the worksheet to add the date above? This is my first macro ever and I tried recording my own-everyone is saying oh its so simple but perhaps I am simple but to me it is not simple at all. I typically "get" stuff like this but just cannot figure it out.

Any extra help for this simple person would be appreciated. I wish there was a tutorial out there that made sense to me, it seems everything is for those who know something.

[JBeaucaire]

Macros are entered into the VBEditor.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[adarshadi24]

Hi,

I've an issue here.... I have 40 spreadsheets and at the end of the day I've to consolidate it to one sheet. Can anybody tell me how to do it??

Please share your thoughts and ideas....

[Paul]

Adarshadi,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[JBeaucaire]

Post #2 of this thread provides a link to a macro designed to do this very thing.

If you can, use that macro, read and learn from it and install it into your workbook (install instructions also listed in this thread) and run it to create your own Consolidate worksheet.

If you can't, start your own thread as per the Forum Rules, attach a sample workbook with a few of your data sheets and your Consolidate sheet and many will chip in to assist.

I think the macro linked on post #2 should take care of your need.