Copy and Paste into 30 different workbooks

[Newbie877]

Hi All,

I have a master file, with about 30 names in it. I am trying to copy each of those names into a separate workbook, which would mean 30 separate workbooks for each name. Is this something I can do with a Macro? Please help!

Thanks

 

[alansidman]

Yes. Provide a sample of what your data looks like and a macro that fits your needs can be written. Make sure that there is enough data to demonstrate what your raw data resembles.

 

[Newbie877]

Yes. Provide a sample of what your data looks like and a macro that fits your needs can be written. Make sure that there is enough data to demonstrate what your raw data resembles.

Please see below a sample. Not sure how to attach a document to my response :/ I'm basically wanting each name (in the sample) to have its own file. Thank you so much.

Name

Salary

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Header

Bob

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Andrew

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Michael

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Jeff

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Carly

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Name

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

<colgroup><col width="70" span="20" style="width:53pt"> </colgroup><tbody>
</tbody>

 

[alansidman]

This code assumes that you have created a worksheet in the workbook with the name of each EE

Option Explicit


Sub Newbie877()
    Dim ws As Worksheet
    Dim i As Long, nm As String
    Dim lr As Long, lrX As Long
    Dim m As Worksheet
    Set m = Sheets("Master")
    m.Range("A1:T1").Copy
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ws.Range("A1").PasteSpecial xlPasteValues
        End If
    Next ws


    lr = m.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        m.Range("A" & i).EntireRow.Copy
        nm = m.Range("A" & i)
        lrX = Sheets(nm).Range("A" & Rows.Count).End(xlUp).Row
        Sheets(nm).Range("A" & lrX + 1).PasteSpecial xlPasteValues
    Next i
    Application.CutCopyMode = False




End Sub

 

[My Aswer Is This]

You subject title says:

Copy and Paste into 30 different workbooks

Do you really mean workbooks or do you mean Worksheets?

​

 

[Newbie877]

Thank you so much! Appreciate this!

This code assumes that you have created a worksheet in the workbook with the name of each EE

Option Explicit


Sub Newbie877()
    Dim ws As Worksheet
    Dim i As Long, nm As String
    Dim lr As Long, lrX As Long
    Dim m As Worksheet
    Set m = Sheets("Master")
    m.Range("A1:T1").Copy
    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ws.Range("A1").PasteSpecial xlPasteValues
        End If
    Next ws


    lr = m.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        m.Range("A" & i).EntireRow.Copy
        nm = m.Range("A" & i)
        lrX = Sheets(nm).Range("A" & Rows.Count).End(xlUp).Row
        Sheets(nm).Range("A" & lrX + 1).PasteSpecial xlPasteValues
    Next i
    Application.CutCopyMode = False




End Sub