Report

Autofill Master sheet [Solved]

Ask a question qwertyAlex - Last answered on Feb 9, 2017 at 06:39 AM by vcoolio
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 
Helpful
+1
plus moins
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.
Was this answer helpful?  
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!