Auto-copying to separate sheets

[rjustanik]

We have an online form that populates to an Excel spreadsheet. We would like the rows to automatically copy to separate sheets (tabs) based on the information in column A. Column A will be co-workers' names and the sheets will allow them to view only their accounts in their own sheet (tab). Can someone help with that?

Thanks!!!

 

[vcoolio]

Hello Rjustanik,

The following code placed in a standard module should do the task for you:-

Sub TransferData()

Application.ScreenUpdating = False

            Dim lr As Long
            Dim MySheet As String
            Dim cell As Range

lr = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lr)
            MySheet = cell.Value
            cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(3)(2)
Next cell

MsgBox "Data transfer completed."

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Following is the link to my test work book for you to peruse. Click on the button to see the code at work:-

https://www.dropbox.com/s/74kkbahbtpg9cpb/Rjustanik(Master sheet to multi sheets).xlsm?dl=0

I'm not sure if you would like the "used" data in sheet1 (or your Input sheet) cleared once transferred to each individual sheet or if the online form overwrites previous data. Please advise.

I hope that this helps.

Cheerio,
vcoolio.

 

[rjustanik]

For some reason it wouldn't run the marco on my computer but I'm sure its just my computer. We do what a main page with all of the information in one place. Does this one do that? Thanks so much!!!!!

Hello Rjustanik,

The following code placed in a standard module should do the task for you:-

Sub TransferData()

Application.ScreenUpdating = False

            Dim lr As Long
            Dim MySheet As String
            Dim cell As Range

lr = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lr)
            MySheet = cell.Value
            cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(3)(2)
Next cell

MsgBox "Data transfer completed."

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Following is the link to my test work book for you to peruse. Click on the button to see the code at work:-

https://www.dropbox.com/s/74kkbahbtpg9cpb/Rjustanik(Master sheet to multi sheets).xlsm?dl=0

I'm not sure if you would like the "used" data in sheet1 (or your Input sheet) cleared once transferred to each individual sheet or if the online form overwrites previous data. Please advise.

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

Hello Rjustanik,

Does this one do that?

If you were able to access my test work book, you'll see that the code will search Column A in the Input sheet (sheet1 in this case) for each individual worker's name and then take the relevant row of data associated with that name and transfer the data to the individual sheet. This will happen once sheet1 has been populated from the external source and you run the code.

If you believe that your computer is the problem, see if you can access another PC and then try again on it.

Cheerio,
vcoolio.