Autofill Master sheet

Solved/Closed
Report
-
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
-
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.

1 reply

Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month