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

- - Latest reply: vcoolio
Posts
1205
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 21, 2018
- Sep 25, 2015 at 04:07 AM
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...


See more 

2 replies

Posts
1205
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 21, 2018
0
Thank you
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.
That worked excellent!. Thank you for your time!!!
Posts
1205
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 21, 2018
0
Thank you
Hello Ryan,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.