Autofill Master sheet Solved/Closed

Question

I am trying to automatically fill in a table (with whole rows from A-N) on a master sheet (sheet 1) based on whether the data in column H of the tables on sheets 2-11 have "no" in the cell.
The tables on the sheets are all identical and go from A-N and 5-22.

vcoolio · Answer

Hello QuertyAlex,
Perhaps the following code may help:-
Sub Transfer()

Dim ws As Worksheet

Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(1).ClearContents

For Each ws In Worksheets
    If ws.Name <> "Master" Then
With ws.Range("H5", ws.Range("H" & ws.Rows.Count).End(xlUp))
        .AutoFilter 1, "No"
        On Error Resume Next
        .Offset(1).EntireRow.Copy
        Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        Sheet1.Columns.AutoFit
        Sheet1.[A1].Select
        End With
        ws.AutoFilterMode = False
        End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub
It will filter Column H of each sheet for the criteria "No" and then transfer the relevant row of data to the Master sheet. It will work on any number of sheets that you may have.
I have prepared a small sample for you to play with at the following link:-
https://www.dropbox.com/s/9wwprntjgzybaex/QuertyAlex.xlsm?dl=0
Click on the "RUN" button to see it work.
I hope that this helps.
Cheerio,
vcoolio.

Autofill Master sheet

1 response

Similar discussions

Subscribe To Our Newsletter!