Copy data from Main Sheet to multiple sheets based on a criteria

I'm very new to this, but I was wondering if it's possible to write a macro that will allow a workbook to copy data from one worksheet (Main Data Sheet) to the other worksheets in the workbook.  All the worksheets have the same headers in row 1.  Column G in the "Main Data Sheet" consists of different vendor names (Vendor 1, Vendor 2, and Vendor 3).  Each vendor has its own worksheet.  I would like to be able to update the "Main Data Sheet" with new order information and based on the Vendor Name displaying in Column G, I would like that new information to be copied over to its respective Vendor Worksheet on a new blank row so that it does not override the previously copied data.  I do not even know where to begin, but any guidance will be helpful.  Thanks in advance!
Working-File.xlsx
Bobby FAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
You can use this macro

Sub post2vendorsht()
    Dim sws As Worksheet
    Dim tws As Worksheet
    Dim cel As Range
    Set sws = Sheets("Main Data Sheet")
    For Each cel In sws.Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
        Set tws = Sheets(cel.Value)
        cel.EntireRow.Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next cel
End Sub
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bobby FAuthor Commented:
Thanks for your comment, Syed.  Can you explain the coding a little?  it's giving me a script error that refers to

Set tws = Sheet(cel.Value)

Is the coding above only for copying the data from the Main Data Sheet?
0
Saqib Husain, SyedEngineerCommented:
It will copy all rows from row 2 to the end from the Main Data Sheet to the respective vendor sheet.

Are you using the code on the same workbook you uploaded or another workbook?

What is the error and on which line?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bobby FAuthor Commented:
Hi, I'm using it on a different workbook.  The actual workbook I need the macro for has sensitive information so I cannot upload it to this site.  So does the coding automatically recognize the sheet based on the name of the vendor in Column I?
0
Saqib Husain, SyedEngineerCommented:
The vendor sheet is selected based on the value in column I. You must make sure that the spellings are exactly same especially leading and trailing spaces. You still have not reported the error you are getting.
0
Bobby FAuthor Commented:
Sorry about that. I'm getting the Run-Time error '9': subscript out of range
0
Saqib Husain, SyedEngineerCommented:
When you get the error click on Debug

If the yellowed line is
Set tws = Sheets(cel.Value)
then select cel.value and press F9. You will see the vendor name. Make sure that a sheet with that name exists.

If the yellowed line is not
Set tws = Sheets(cel.Value)
then tell me which line it is.
0
Roy CoxGroup Finance ManagerCommented:
It seems a pointless thing to do. All data is best maintained on one sheet ans simply use AutoFilter to view individual vendors

If you really want to do this then this code will do what you want. Automating AutoFilter will be much faster than a Loop. You don't say if you intend keeping existing data in the main Sheet.

Option Explicit

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 24/09/2006 22:48
' Updated   : 2014
' Author    : Roy Cox (royUK)
' Website   :  more examples
' Purpose   :  Create a sheet for each unique name in data
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.
'---------------------------------------------------------------------------------------

Sub ExtractToSheets()
    Dim ws     As Worksheet
    Dim wsNew  As Worksheet
    Dim rData  As Range
    Dim rCl    As Range
    Dim sNm    As String
    Set ws = Sheet1

    'extract a list of unique names
    'first clear existing list
    With ws
        Set rData = .Range("A1").CurrentRegion
        .Columns(.Columns.Count).Clear
        rData.Columns(9).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

        For Each rCl In .Cells(1, .Columns.Count).CurrentRegion
            sNm = rCl.Text
            'add new sheet (only if required-NB uses UDF)
            If WksExists(sNm) Then
                'so clear contents
                Sheets(sNm).Cells.Clear
            Else
                'new sheet required
                Set wsNew = Sheets.Add
                wsNew.Move After:=Worksheets(Worksheets.Count)    'move to end
                wsNew.Name = sNm
            End If
            'AutoFilter & copy to relevant sheet
            rData.AutoFilter Field:=9, Criteria1:=sNm
            rData.Copy Destination:=Worksheets(sNm).Cells(1, 1)
        Next rCl
    End With
    ws.Columns(Columns.Count).ClearContents        'remove temporary list
    rData.AutoFilter        'switch off AutoFilter
End Sub

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Open in new window

Working-File.xlsm
1
Bobby FAuthor Commented:
Thank you so much Syed.  It worked!
0
Roy CoxGroup Finance ManagerCommented:
As I said AutoFilter will be so much faster than a loop!
0
Bobby FAuthor Commented:
Thank you, Roy.  Is there anyway to change your coding so that it does not create a new worksheet called "Vendor Name"?  It thinks the header is the data I want to copy.  The data also isn't copying over to their individual worksheets.
0
Bobby FAuthor Commented:
Sorry Roy, I just realized that there's another section in the coding that references column 9.  In the actual file I'm working on, the column is a different number and I only changed the column number in the beginning.  Your coding works perfectly without any run-time error when I encounter blank rows.  Thank you!!!
0
Roy CoxGroup Finance ManagerCommented:
Thanks for letting me know
0
vivek aroraCommented:
Dear Saqib Sir,

Your macro seems to work fine but it starts from row 1 instead of row 2. Please review the solution once.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.