Data Consolidation while excluding other sheets

Question

this might be answered already from other posts I have read but still struggling to figure it out.

I have a workbook with 85 worksheets on it. Each sheet is like an invoice format, meaning it is not formatted as a normal data set. In order for me to get the data only I need, i created helper columns which only selects the data I need for consolidation. So I have a range I13:N42 which contains the data I need to consolidate.

At the end of the workbook, I already set up a Master Sheet with all the necessary headers for the data set. And there are 2 more worksheets namely "Tracking" & "AppControl" but I dont want them to be included in the loop together with the Master sheet.

For my range (filled with cell references/formulae), I need to copy only the row that has data in it.

You might have some ideas to improve the code I am currently using.

Sub Combine()

   Dim i As Integer
   Dim ws As Worksheet
   Dim rng As Range

   On Error Resume Next

   For i = 1 To Sheets.Count
    
      Sheets(i).Activate
      Range("I13:N42").Select
      Selection.Copy Destination:=Sheets("Master").Range("A65536").End(xlUp)(2)

   Next i

End Sub

score 0 · Accepted Answer · answered Sep 09 '21 at 06:55

First remove On Error Resume Next. This line hides all error messages but the errors still occour, you just cannot see their messages. So if there are errors you cannot see you cannot fix them. If you don't fix them your code cannot work. Remove that line and fix your errors! Also see VBA Error Handling – A Complete Guide.

Second Avoid using Select in Excel VBA. That is a very bad practice and makes your code unreliable!

Option Explicit

Public Sub Combine()
    Dim wsMaster As Worksheet  ' set master worksheet
    Set wsMaster = ThisWorkbook.Worksheets("Master")

    Dim ExcludeWorksheets As Variant  ' define worksheets names to exclude
    ExcludeWorksheets = Array(wsMaster.Name, "Tracking", "AppControl")

    Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Not IsInArray(ThisWorkbook.Worksheets(i).Name, ExcludeWorksheets) Then 'exclude these worksheets
            ThisWorkbook.Worksheets(i).Range("I13:N42").Copy Destination:=wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp)(2)
        End If
    Next i
End Sub

Public Function IsInArray(ByVal StringToBeFound As String, ByVal Arr As Variant) As Boolean
    IsInArray = (UBound(Filter(Arr, StringToBeFound)) > -1)
End Function

Alternatively you can use a For Each loop which looks a bit cleaner then

Option Explicit

Public Sub Combine()
    Dim wsMaster As Worksheet  ' set master worksheet
    Set wsMaster = ThisWorkbook.Worksheets("Master")

    Dim ExcludeWorksheets As Variant  ' define worksheets names to exclude
    ExcludeWorksheets = Array(wsMaster.Name, "Tracking", "AppControl")

    Dim ws As Worksheet
    For Each ws Is ThisWorkbook.Worksheets
        If Not IsInArray(ws.Name, ExcludeWorksheets) Then  'exclude these worksheets
            ws.Range("I13:N42").Copy Destination:=wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp)(2)
        End If
    Next ws
End Sub

Hi, I appreciate your immediate response to my question. I tried the code you gave and it did work. I tried changing the defined range because my previous range consists of formulas and cell referencing because the code copies the whole range including the formula. I need to copy the rows that has data in it. — SoulSeeker916, Sep 09 '21 at 07:32
@SoulSeeker916 Is that a question? Then I don't understand what your issue is. Please open a new question for it, we can only handle one question per post. Describe your problem well and give example data (screenshots might help too). — Pᴇʜ, Sep 09 '21 at 11:41

Data Consolidation while excluding other sheets

1 Answers1