Previous Topic Next Topic

Autofill Master sheet

Solved/Closed
qwertyAlex - Feb 7, 2017 at 07:32 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Feb 9, 2017 at 06:39 AM
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.
Related:

1 response

Answer 1 / 1
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Feb 9, 2017 at 06:39 AM
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