copying data from one table to many

[rtandrew]

I am looking for a way to copy data rows from one master sheet into other existing excel files based on codes located in column "A" of the master sheet. There are column headers in both the master and individual sheets, with the data rows starting on row 4. Is there a way to do this without triggering the "save as"?

Thanks.

 

[ktfidler]

Andrew - Could this be modified to request a specific entry in Col A so it would create a new workbook for only data that matched the prompt instead of all entries in the column?

Thank you,

KFidler

Here is some code that will copy columns A:H on Sheet1 to new workbooks named after the entries in column A:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim c As Range
    Dim List As New Collection
    Dim Item As Variant
    Dim WB As Workbook
    Application.ScreenUpdating = False
''   *** Change Sheet name to suit ***
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
    On Error Resume Next
    For Each c In Rng
        List.Add c.Value, CStr(c.Value)
    Next c
    On Error GoTo 0
    Set Rng = Sh.Range("A1:H" & Sh.Range("A65536").End(xlUp).Row)
    For Each Item In List
        Set WB = Workbooks.Add
        Rng.AutoFilter Field:=1, Criteria1:=Item
        Rng.SpecialCells(xlCellTypeVisible).Copy WB.Worksheets(1).Range("A1")
        Rng.AutoFilter
        With WB
            .SaveAs ThisWorkbook.Path & "\" & Item & ".xls"
            .Close
        End With
    Next Item
    Sh.Activate
    Application.ScreenUpdating = True
End Sub

Maybe yo can adapt it to suit.