Combine Tabs into One Master Sheet

zxox · Apr 5th 2013

I have the following code; however, I need it to be tweaked.

1. I would like the unique tab names to appear in Column A (currently unused).
2. Paste ALL rows, even blank from b2:q61.

All sheets are the same with the exception of the code

Code

Sub CopySheetsToSummary()
     
    Dim ws As Worksheet
    Dim wsDest As Worksheet
    Dim RangeToCopy As Range
    Dim RangeToPaste As Range
     
    Set wsDest = Worksheets("Summary")
     
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> wsDest.Name Then
            Set RangeToCopy = ws.Range("a11", ws.Range("a65536").End(xlUp)).EntireRow
            Set RangeToPaste = wsDest.Range("a65536").End(xlUp).Offset(2, 0)
            If RangeToPaste.Address = "$A$3" Then Set RangeToPaste = Range("a11")
            RangeToCopy.Copy RangeToPaste
        End If
    Next
End Sub

Display More

Smallman · Apr 5th 2013

Re: Combine Tabs into One Master Sheet

Hi zxox

Welcome to Ozgrid

You can't do this the way you are attempting. You are copying the entire row which means it has to be pasted in Col A of your summary tab. What is the shape of each of your Input tabs. I would suggest you set the sheet out the way the attached workbook is created. No need to test for row 3 either. Just use a simple tool called a header. The following code goes with the attached file.

Code

Option Explicit


Sub Teeso()
Dim ws As Worksheet
Dim lr As Integer
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            ws.Range("A11", ws.Range("Q65536").End(xlUp)).Copy Sheet1.Range("B65536").End(xlUp)(2)
            Sheet1.Range("A65536").End(xlUp)(2).Value = ws.Name
            lr = Sheet1.Range("A65536").End(xlUp).Row
            Range("A" & lr & ":A" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown
        End If
    Next ws
End Sub

Display More

Take care

Smallman

**royUK** · Apr 6th 2013

Re: Combine Tabs into One Master Sheet

Alternative way

Code

Option Explicit


'---------------------------------------------------------------------------------------
' Procedure : Combinedata
' Author    : Roy Cox
' Website   : www.excel-it.com
' Date      : 10/10/2010
' Amended   : 06/04/2013
' Purpose  : Combine data from all sheets to a master sheet
'---------------------------------------------------------------------------------------
'
Sub Combinedata()


    Dim ws As Worksheet
    Dim wsMain As Worksheet
    Dim DataRng As Range
    Dim Rw As Long
    Dim Cnt As Integer
    Const ShtName As String = "Summary"    '<-destination sheet here
    Const sRw As Long = 10  'data starts in Row10


    Cnt = 1


    Set wsMain = Worksheets(ShtName)
    wsMain.Cells.Clear


    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsMain.Name Then
            Set DataRng = ws.Cells(sRw, 1).CurrentRegion
            If Cnt = 1 Then    '<- first run
               DataRng.Copy wsMain.Cells(sRw, 1)
               Cnt = Cnt + 1 '<- increase cnt to prevent copying headers subsequently
            Else: Rw = wsMain.Cells(wsMain.Rows.Count, 1).End(xlUp).Row + 1
                'don't copy header rows
                With DataRng
                    .Offset(1, 0).Resize(.Rows.Count - 1, _
                                         .Columns.Count).Copy wsMain.Cells(Rw, 1)
                End With
            End If
        End If
    Next ws


End Sub

Display More

zxox · Apr 6th 2013

Re: Combine Tabs into One Master Sheet

Thank you for the warm welcome and the replies. Neither of these worked for my data.

I am essentially trying to copy and paste all data rows from B11:Q61 into one sheet. I have about 100 files (each with at least 5 tabs) that are in this same format and would like to get them into one master file.

Maybe you have a better way to accomplish this? Thanks again for your help!

cytop · Apr 6th 2013

Re: Combine Tabs into One Master Sheet

Quote

...Neither of these worked for my data

Then please upload a sample workbook that contains a small sample of your data, suitably anonymised if necessary. You've had 2 possible solutions based on your description of your data, now it's time to actually see what your data actually looks like.

zxox · Apr 6th 2013

Re: Combine Tabs into One Master Sheet

I will make a new spreadsheet for you as I can not provide you with the original information. I'd get fired! I will post shortly.

zxox · Apr 6th 2013

Re: Combine Tabs into One Master Sheet

See attached sample. I have too many files with tabs to do copy paste 1000x. I want to be able to manipulate the data from one master. I need the tab name to be able to reference back to it.

forum.ozgrid.com/index.php?attachment/52719/

**royUK** · Apr 6th 2013

Re: Combine Tabs into One Master Sheet

How do you want to combine that data??

zxox · Apr 6th 2013

Re: Combine Tabs into One Master Sheet

All in one sheet -- don't care what it's called. See the summary sheet of my upload.

**royUK** · Apr 7th 2013

Re: Combine Tabs into One Master Sheet

I can't see any point in doing this.

This code will copy each sheet into the summary sheet, howver you need to make each sheet's form uniform, i.e. use the same range for the form

Code

Option Explicit


Sub CombineSheets()
    Dim wsMain As Worksheet, ws As Worksheet
    Dim lRw As Long


    Set wsMain = Worksheets("Summary")


    wsMain.Cells.Clear


    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsMain.Name Then
            If lRw = 0 Then
                lRw = 2
            Else: lRw = lRw + 57
            End If
            ws.Range("B2:O56").Copy wsMain.Cells(lRw, 2)
        End If
    Next ws


End Sub

Display More

zxox · Apr 8th 2013

Re: Combine Tabs into One Master Sheet

Quote from royUK;658351

I can't see any point in doing this.

I completely understand this statement! HA! But, trust me this helps immensely! A MILLION THANK YOUS FOR WORKING CODE! :yourock:

**royUK** · Apr 8th 2013

Re: Combine Tabs into One Master Sheet

No problem, remember to use a uniform page setup for each sheet

zxox · Apr 8th 2013

Re: Combine Tabs into One Master Sheet

Will do! Thanks again!

Combine Tabs into One Master Sheet

Participate now!

Share