AutoFilter by sheet name

[pook_666]

Hi magicians,

I am currently got a script that creates new worksheets and renames them based on a range.

Once the first sheet is created, I want to do an autofilter on column A of another worksheet called "Data" based on the newly created sheet name and copy this data to this newly created worksheet (cell A1 is fine). Then go onto the next created worksheet and do the same through a loop.

I know this is a hell of a lot to ask, involving many things, but if anyone could help start me off with the autofilter based on the worksheet name that would be a great help as this is where I am really stuck and have no clue of how to link the autofilter criteria to the worksheet.

My current code is the below....maybe it's probably best to go to another macro to do all of this where I have put my note in below? Really stuck with this autofilter part!

Dim MyCell As Range
Dim MyRange As Range
Dim ws As Worksheet

'add sheets
    Set ws = Sheets("Brokers")
    Set MyRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

    For Each MyCell In MyRange
        Sheets.Add Before:=Sheets("Setup")
        ActiveSheet.Name = MyCell.Value
        
    'insert code to autofilter based on newly created sheet name, then copy this data into the new sheet - help please!
    
    Next MyCell

Thanks in advance!

 

[MARK858]

and copy this data

What columns are getting copied?

 

[pook_666]

@MARK858 - all the columns that are getting filtered, in this case A:K

 

[MARK858]

Try the below (untested)...

    For Each mycell In MyRange
    
        Sheets.Add Before:=Sheets("Setup")
        ActiveSheet.Name = mycell.Value
        
        With Sheets("Data").Range("A1:A" & Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row)
    
            .AutoFilter 1, mycell.Value
        
            On Error Resume Next
            .Offset(1).Resize(.Rows.Count - 1, 11).SpecialCells(12).Copy Sheets(mycell.Value).Range("A1")
            On Error GoTo 0
            .AutoFilter
    
        End With
    
    Next mycell

 

[pook_666]

This works perfectly - thank you very much!

I did change

.Offset(1)

to

.Offset(0)

so that it included the headings from the autofilter when copied over (just an FYI if anyone else wanted to know!)

 

[MARK858]

This works perfectly - thank you very much!

You're welcome

I did change .Offset(1) to .Offset(0)so that it included the headings from the autofilter

If that is the case then just remove the Offset and adjust the Resize (if you didn't adjust the resize when you changed the Offset then in certain circumstances you would lose a row when doing a copy)...

 .Offset(1).Resize(.Rows.Count - 1, 11).SpecialCells(12).Copy Sheets(mycell.Value).Range("A1")

to

.Resize(, 11).SpecialCells(12).Copy Sheets(mycell.Value).Range("A1")