Consolidation macro - specfic rows from multiple worksheets based on set criteria into a master

[jsbaker88]

Hi

I have 10 sheets and 1 master. Each of the 10 sheets represents a set of activity from one particular client which is being compared against our bookings for the day for accuracy and completeness (a rec..). If data matches, our reference is added to column B of each sheet. If there is a mismatch or something is missing, each sheet flags our staff to check the booking.

Now, we are planning to have many more clients so it will be much easier having one master summary sheet. Column headings A-H from each sheet are the same as the master. I would like a macro which populates a 'Breaks tab', taking data in these columns whereby cell in column B (from B2 down) from each sheet contains "check". Once it finds check that row should be added to the master. Each sheet has a different number of rows populated and blanks.

Praying someone can help as I'm a VBA virgin...I know what I want just no idea how to get it.

Thanks
James

 

[JackDanIce]

Welcome to the board. Try:

Sub Compile_Recs()

Dim i as Long, j as Long
Dim Master_Wks as Worksheet
Set Master_Wks = Sheets("Master")

Application.ScreenUpdating = false

For i = 1 to Worksheets.Count
  With Sheets(i)
    If .AutoFilterMode Then .AutoFilterMode = False
    If .Name <> Master_Wks.Name Then
      j = .Range("A" & Rows.Count).End(xlUp).Row
      With .Range("A1:H" & j)
        .AutoFilter
        .AutoFilter Filed:=2, Criteria1:="Check"
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Master_Wks.Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilterMode = False
      End With
    End if
  End with
Next i

Master_Wks.Select

With Application
  .CutCopyMode = False
  .ScreenUpdating = True
End With

End Sub

End Sub

[/code]

 

[jsbaker88]

Thank you...and sorry if this is obvious...

I get a complile error: sub or function not defined, highlighting AutoFilter under with .Range

Any ideas?

 

[JackDanIce]

My error - typo, I didn't test it myself! In red is the change but try:

Sub Compile_Recs()

Dim i as Long, j as Long
Dim Master_Wks as Worksheet
Set Master_Wks = Sheets("Master")

Application.ScreenUpdating = false

For i = 1 to Worksheets.Count
  With Sheets(i)
    If .AutoFilterMode Then .AutoFilterMode = False
    If .Name <> Master_Wks.Name Then
      j = .Range("A" & Rows.Count).End(xlUp).Row
      With .Range("A1:H" & j)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="Check"
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Master_Wks.Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilterMode = False
      End With
    End if
  End with
Next i

Master_Wks.Select

With Application
  .CutCopyMode = False
  .ScreenUpdating = True
End With

End Sub

 

[jsbaker88]

thank you, still getting an error on the " If .Name <> Master_Wks.Name Then" saying undefined.. wish I was better at all this!

 

[JackDanIce]

No idea what's causing that, but instead, try:

Sub Compile_Recs()

Dim i as Long, j as Long
Dim Master_Wks as Worksheet
Set Master_Wks = Sheets("Master")

Application.ScreenUpdating = false

For i = 1 to Worksheets.Count
  With Sheets(i)
    If .AutoFilterMode Then .AutoFilterMode = False
    If .Name <> "Master" Then
      j = .Range("A" & Rows.Count).End(xlUp).Row
      With .Range("A1:H" & j)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="Check"
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Master_Wks.Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilterMode = False
      End With
    End if
  End with
Next i

Master_Wks.Select

With Application
  .CutCopyMode = False
  .ScreenUpdating = True
End With

End Sub

As before, change in red