Consolidating worksheets (vba code or formula)

COGICPENNY · Apr 14th 2015

I'm trying (without success) to consolidate two worksheets in two separate tabs within the same file. They have come from the same source file that was too large to export all of FY2014 data, so it was broken up into semi-year (Q1-Q2, and Q3-Q4). They have the same exact structure/format, however trying to consolidate them is not working.

The Q1-Q2 file: A1..X553210 with a header row
The Q3-Q4 file: A1..X619378 with a header row

I tried using the following Code I found off the internet, which DID create a new tab called "Consolidated", but it only copied and pasted the Q3-Q4 file cell for cell and row for row (duplicate):

Code

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

Display More

**royUK** · Apr 15th 2015

Re: Consolidating worksheets (vba code or formula)

Have you read the Forum Rules yet?

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.
Please make an immediate edit of your post to add the missing tags. Be sure to use them in future posts.

[COLOR="navy"]How to use code tags[/COLOR]

[noparse]

Code

[/noparse]
[COLOR="navy"]your code goes between these tags[/COLOR]
[noparse]

[/noparse]

Or, just highlight all of the code and press the [COLOR="#FF0000"]#[/COLOR] button to add the code tags.

Thanks.

**royUK** · Apr 15th 2015

Re: Consolidating worksheets (vba code or formula)

here's some code that might need adapting to suit

Code

' Module    : Module1
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : www.excel-it.com for more examples and Excel Consulting
' Purpose   : combine data from multiple sheets to one
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.


'---------------------------------------------------------------------------------------
Option Explicit


Private Sub CommandButton1_Click()


    Dim ws As Worksheet
    Dim DataRng As Range
    Dim Rw As Long, lOffset As Long
    Dim HasHeaders As Boolean




    Worksheets.Add
    ActiveSheet.Name = "Combined"


    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            If HasHeaders Then lOffset = 1
            Rw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + lOffset
            Set DataRng = ws.Cells(1, 1).CurrentRegion
            'don't copy header rows
            DataRng.Offset(1, 0).Resize(DataRng.Rows.Count - 1, _
                                        DataRng.Columns.Count).Copy ActiveSheet.Cells(Rw, 1)
            HasHeaders = True
        End If
    Next ws


End Sub

Display More

Consolidating worksheets (vba code or formula)

Participate now!

Share