Auto populate rows from master sheet to other sheets Excel 2013Solved/Closed

Question

Hello, 

I'm working on a sheet to autopopulate from data that I've downloaded in Excel format.  Currently, I have to recreate the wheel (spreadsheets) each time I download the personnel data (we have a lot of turnover) and I know it can be simpler.

Simply put I would like the "Master" sheet, where the raw data is, to populate the 5 other sheets, which are the areas the personnel are in (MYA1, MYA2, MYA3, MYA4, MYT1).  I would need the macro to populate the rows A2:Q2, A3:Q3, etc from the value of Column A to the corresponding sheets.

The data comes to me as follows:

      A           B          C........... etc, with data through Column Q (or forever)
1)   (Row 1 is the column name)
2)   MYA1     NAME   TITLE    NUMBER.......etc
3)   MYA3     NAME   TITLE    NUMBER.......etc
4)   MYA1     NAME   TITLE    NUMBER.......etc
5)   MYA2     NAME   TITLE    NUMBER.......etc

I would need the macro to search Column A and find a specific value and place the corresponding rows into a separate sheet of that same name.  Of course the result would be something like this for sheet MYA1:

      A           B          C........... etc, with data through Column Q (or forever)
1)   (Row 1 is the column name)
2)   MYA1     NAME   TITLE    NUMBER.......etc
3)   MYA1     NAME   TITLE    NUMBER.......etc
4)   MYA1     NAME   TITLE    NUMBER.......etc
5)   MYA1     NAME   TITLE    NUMBER.......etc

As a foot note, I will need to delete columns from the raw source and insert blank columns.  There is already data that can't pulled from the raw data source that will need to remain. Specifically there would be blank columns in the raw data source for G,H,M,N,O,P so that those columns are not overwritten...or would blank cells be transferred over either way?  Or is this simply not possible?  I suppose I could always reconfigure the spreadsheets to have the data in those columns (G,H,M,N,O,P) at the end of the autopopulated data.  I apologize for the rambling...


Configuration: Windows 7 / Internet Explorer 9.0

vcoolio · Answer

Hello Ryan,

I think that I have understood what you would like to do so see if the following code does as you would like:-


Sub TransferData()

Application.ScreenUpdating = False

Dim lRow As Long
    Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each sht In Worksheets
        If sht.Name <> "Master" Then
        sht.UsedRange.Offset(1).ClearContents
        End If
Next sht

For Each cell In Range("A2:A" & lRow)
    MySheet = cell.Value
    cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


Following is my test work book for you to peruse. Run the macro and the data for each unique ID will be transferred to the respective individual sheet (including blank cells).

https://www.dropbox.com/s/at3a9pumvlxsnkb/RyanLug.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ryan,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.

Auto populate rows from master sheet to other sheets Excel 2013

2 responses

Similar discussions

Subscribe To Our Newsletter!