Marco that filters on each vendor and creates new worksheets for each

[richardjshaffer]

Hi,

hope someone can help, I'd like to run a macro that looks on the set up vendor data, filtering in turn on each vendor in the first column of the set of data and pasting the relevant data into a new worksheet for each unique vendor?

eg

Vendor Address Turnover
a xxxxxx 45
b wwwww 56
a yyyyyyy 78
c uuuuuuu 98

So the macro filters on Vendor, copies both lines relevant to vendor A into a new worksheet, then the line for vendor b into another worksheet, and then the line for vendor c into another worksheet.

Hope this is clear, many thanks,

Richard

 

[mirabeau]

Hi Richard,

is this the kind of code you want? Try it on a test workbook first.

Sub vendors_newsheet()
Dim sh As Worksheet, rws&, cls&
Dim g As Range, e As Range
Set sh = ActiveSheet
Sheets.Add.Name = "tmp"
sh.Activate
    rws = Cells.Find("*", after:=[A1], searchorder:=xlByRows, _
        searchdirection:=xlPrevious).Row
    cls = Cells.Find("*", after:=[A1], searchorder:=xlByColumns, _
        searchdirection:=xlPrevious).Column
With Sheets("tmp")
    sh.Range("A1").Resize(rws, cls).Copy .Range("A1")
    .Activate
    .Range("A1").Resize(rws, cls).Sort .Range("A1"), 1, Header:=xlYes
    Set g = .Range("A2")
For Each e In Range(g, g.End(4)(2))
    If e <> g Then
        Sheets.Add.Name = g.Value
        Range(g, e(0)).Resize(, cls).Copy Sheets(g.Value).Range("A1")
        Set g = e
    End If
Next e
Application.DisplayAlerts = False
    .Delete
Application.DisplayAlerts = True
End With
End Sub

 

[richardjshaffer]

Thank you very much for your help, this works brilliantly.

If I need any slight modifications I might ask again if that's OK,

many thanks again,

Richard

 

[mirabeau]

Thank you very much for your help, this works brilliantly.

If I need any slight modifications I might ask again if that's OK,

many thanks again,

Richard

Glad it worked for you.

Post back whenever you like.

 

[richardjshaffer]

Hi,

please would I be able to make an amendment to the macro we are trying to create? If I could please send you an example file it would be very helpful if you'd be so kind?

thanks,

Richard

 

[Peter_SSs]

please would I be able to make an amendment to the macro we are trying to create? If I could please send you an example file it would be very helpful if you'd be so kind?

Please try to ask your question and/or post screen shots in the public forum or at worst upload a sample file to a public place. Refer to #7 of the Forum Rules.

 

[richardjshaffer]

Apologies, was unaware, I will do.

regards

 

[Peter_SSs]

Thanks.