Report

Autopop Info on Sheet based on Criteria from Master Sheet.

Ask a question MBrownie 1Posts Friday July 22, 2016Registration date July 22, 2016 Last seen - Last answered on Apr 27, 2017 at 03:02 AM by vcoolio
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"
See more 
Helpful
+0
plus moins
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.
EMS360 2Posts Saturday April 15, 2017Registration date April 17, 2017 Last seen - 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
Reply
Leave a comment
Helpful
+0
plus moins
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.
Leave a comment
Helpful
+0
plus moins
Hi Michelle,

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

Cheerio,
vcoolio.
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!