Autofill Master sheet [Solved/Closed]

qwertyAlex - Feb 7, 2017 at 07:32 PM - Latest reply: vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen
- 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.
See more 

1 reply

Best answer
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Feb 9, 2017 at 06:39 AM
1
Thank you
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.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1670 users this month