Autopop Info on Sheet based on Criteria from Master Sheet.

Closed
MBrownie Posts 1 Registration date Friday July 22, 2016 Status Member Last seen July 22, 2016 - Jul 22, 2016 at 02:15 PM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Apr 27, 2017 at 03:02 AM
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"

3 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Jul 22, 2016 at 07:35 PM
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.
0
EMS360 Posts 2 Registration date Saturday April 15, 2017 Status Member Last seen April 17, 2017
Apr 17, 2017 at 02:07 PM
Hello Vcoolio,

I believe I have the same inquiry for my situation: auto populate sub sheets based on master sheet. If I send you a sample of my workbook, can you help me with this as well?

Thank you in advanced for any help you can offer - really appreciate it.

Michelle
EMS360
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Apr 17, 2017 at 06:41 PM
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.
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Apr 27, 2017 at 03:02 AM
Hi Michelle,

I'm back! Do you still require help or have you managed to sort it out?

Cheerio,
vcoolio.
0