Consolidate worksheets all but one.

[fvisions]

I have this code to consolidate 7 worksheets into one and it works great. I was then asked to add a tab for instructions for the end user with a form control button for the process. When I do this the code no longer works right. How do I update the code to ignore the "Instruction" worksheet?

Dim wrk As Workbook
    Dim sht As Worksheet
    Dim trg As Worksheet
    Dim rng As Range
    Dim colCount As Integer
     
    Set wrk = ActiveWorkbook
     
    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
     
     Application.ScreenUpdating = False
     Set trg = wrk.Worksheets.Add(Before:=wrk.Worksheets(wrk.Worksheets.Count))
     trg.name = "Master"
     Set sht = wrk.Worksheets(1)
     colCount = sht.Cells(1, 255).End(xlToLeft).Column
     With trg.Cells(1, 1).Resize(1, colCount)
        .Value = sht.Cells(1, 1).Resize(1, colCount).Value
        .Font.Bold = True
    End With
     For Each sht In wrk.Worksheets
        If sht.Index = wrk.Worksheets.Count Then
            Exit For
        End If
    Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(1048576, 1).End(xlUp).Resize(, colCount))
    trg.Cells(1048576, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    Next sht
    trg.Columns.AutoFit
    Application.ScreenUpdating = True
    
  Columns("A:A").Select
  Selection.NumberFormat = "0000"
  Columns("B:B").Select
  Selection.NumberFormat = "0"
  Columns("D:D").Select
  Selection.NumberFormat = "mm/dd/yyyy"
  Columns("F:F").Select
  Selection.NumberFormat = "mm/dd/yyyy"
 Range("A1").Select
  Worksheets("MASTER").Cells.EntireColumn.AutoFit
    Worksheets("MASTER").Move Before:=Worksheets("9459")

 

[Fluff]

How about

For Each sht In wrk.Worksheets
   If LCase(sht.Name) <> "instruction" Then
      Set Rng = sht.Range(sht.Cells(2, 1), sht.Cells(1048576, 1).End(xlUp).Resize(, colCount))
      trg.Cells(1048576, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
   End If
Next sht

 

[JLGWhiz]

Modify loop as shown below:

For Each sht In wrk.Worksheets
    If sh.Name <> "Instruction" Then        
        Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(1048576, 1).End(xlUp).Resize(, colCount))
        trg.Cells(1048576, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    End If
Next sht

 

[fvisions]

How about

For Each sht In wrk.Worksheets
   If LCase(sht.Name) <> "instruction" Then
      Set Rng = sht.Range(sht.Cells(2, 1), sht.Cells(1048576, 1).End(xlUp).Resize(, colCount))
      trg.Cells(1048576, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
   End If
Next sht

Question, does this replace code or do I add this? I still need the "Master" worksheet.

 

[Fluff]

It replaces this part of your code

     For Each sht In wrk.Worksheets
        If sht.Index = wrk.Worksheets.Count Then
            Exit For
        End If
    Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(1048576, 1).End(xlUp).Resize(, colCount))
    trg.Cells(1048576, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    Next sht