+ Reply to Thread
Results 1 to 3 of 3

Consolidating Worksheets with VBA

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    Smile Consolidating Worksheets with VBA

    Hi Everyone,

    I am working on a project for fun but I am struggling to consolidate a few worksheets (not all worksheets) with VBA into a master worksheet.

    The idea is to copy all data over from tabs with "ABC" in the worksheet name, i.e. copy from "ABC 1", "ABC 2", "ABC A" but not "XYZ".

    All the ABC worksheets have the same header, so I would like to retain this only once / the first time when consolidating the data.

    If anyone can figure out the code, that would be really helpful!


  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    MS-Off Ver
    Excel 2010 on Linux - O365

    Re: Consolidating Worksheets with VBA

    Have a look at https://www.rondebruin.nl/win/s3/win002.htm

  3. #3
    Registered User
    Join Date
    MS-Off Ver

    Re: Consolidating Worksheets with VBA

    I have come across that site, however, the code I copied and altered does not seem to show an output in the "Master ABC" tab.

    This is what I have so far. Any thoughts on where it is going wrong or how to fix it?


    Module 1

    Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
    After:=sh.Range("A1"), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    On Error GoTo 0
    End Function

    Module 2

    Sub Consolidate_ABC()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    'Delete the sheet "Master ABC" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Master ABC").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "Master ABC"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Master ABC"

    'Fill in the start row
    StartRow = 2

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
    If LCase(Left(sh.Name, 4)) = "ABC " Then

    'Find the last row with data on the DestSh
    Last = LastRow(DestSh)

    'Fill in the range that you want to copy
    Set CopyRng = sh.Range("A1").CurrentRegion

    'Test if there enough rows in the DestSh to copy all the data
    If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
    MsgBox "There are not enough rows in the Destsh"
    GoTo ExitTheSub
    End If

    'Copy header row, change the range if you use more columns
    If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
    sh.Range("A1:AZ1").Copy DestSh.Range("A1")
    End If

    'This example copies values/formats, if you only want to copy the
    'values or want to copy everything look at the example below this macro
    With DestSh.Cells(Last + 1, "A")
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    End With

    End If


    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Consolidating worksheets
    By Tendekai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2018, 12:43 PM
  2. [SOLVED] Consolidating worksheets
    By davidman1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-06-2012, 02:26 PM
  3. Consolidating worksheets and then referencing the worksheets themselves in a new column
    By zed commander in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2012, 05:14 AM
  4. Consolidating worksheets
    By Cand in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-09-2009, 05:43 PM
  5. Consolidating Worksheets
    By ctf in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2008, 04:48 PM
  6. [SOLVED] Consolidating worksheets
    By Joeflo in forum Excel General
    Replies: 1
    Last Post: 04-22-2006, 06:47 PM
  7. [SOLVED] Consolidating multiple worksheets
    By Laura T. in forum Excel General
    Replies: 1
    Last Post: 08-16-2005, 07:05 PM

Tags for this Thread


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