Autopop Info on Sheet based on Criteria from Master Sheet. Closed

Question

Hi All, 
I am creating a Master spreadsheet that has information across rows. I want this information to auto-populate on a different spreadsheet depending on the criteria in column A. 

Example on the Master Sheet  I have Column A (this column has a name with a corresponding sheet for the name) and I have info across the rows from B-N. In Column A I have the name Matt as the criteria for rows 3, 5, 10 and 11. How do I get the info from those rows that have Column A's criteria of Matt to auto-populate on the corresponding "Matt" Sheet?

Overall I will have 5 different Name options for Column A and will have 5 corresponding sheets. I want to be able to enter the name option in Column A on the Master sheet and have the row info populate on that person's "Sheet"

vcoolio · Answer

Hello MBrownie,

Have a look at the following link to a test work book I created for another Poster a couple of months ago:-

https://www.dropbox.com/s/hzqz9opl8cl724i/Dani%28Master%20sht%20to%20multi%20shts%2Cautofilter%29.xlsm?dl=0

I believe that this is a similar scenario to yours and should work for you with a couple of minor changes.

Following is the code associated with the test file:-

Option Explicit
Sub TransferData()

Dim ar As Variant
        Dim i As Integer
        
ar = Array("Big", "Little", "Medium")

Application.ScreenUpdating = False

For i = 0 To UBound(ar)
         Sheets(ar(i)).Offset(1).UsedRange.ClearContents
         Sheet1.Range("K2", Sheet1.Range("K" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i)
         [A2].CurrentRegion.Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)
         Sheets(ar(i)).Columns.AutoFit
    Next i
[K2].AutoFilter
 
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

In line 7 of the code, change the array titles to the names of your work sheets ("Matt", "Tom", etc. etc.). Spell them exactly as per the sheet names.

In line 13, change any "K" that you see to "A" as your criteria (names) are in Column A. If your headings are in row 1, then change the K2 to A1.

In line 19, change the "K" to "A". Again, if your headings are in row 1, change the K2 to A1.

It should all then work for you but test it first in a copy of your work book. Run the code from your Master sheet.
I also assume that all the sheets are set up the same.

In the test file above, click on the "RUN" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hi Michelle,

I sure can help but I am away from home and my PC at the moment and will be for a few days yet. I can't do this successfully on my phone and the wife will kill me if she catches me doing this anyway whilst we are away!
But yes, upload a sample of your work book to a free file sharing site such as Dropbox, ge.it  or Sendspace and then post the link to your file back here. Please use dummy data. Also, It may be worth starting your own thread.

Cheerio,
vcoolio.

vcoolio · Answer

Hi Michelle,

I'm back! Do you still require help or have you managed to sort it out?

Cheerio,
vcoolio.

Autopop Info on Sheet based on Criteria from Master Sheet.

3 responses

Similar discussions

Subscribe To Our Newsletter!