AutoFilter by sheet name

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
68
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!

VBA Code:
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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try the below (untested)...

VBA Code:
    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
 
Solution

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
68
This works perfectly - thank you very much!

I did change
VBA Code:
.Offset(1)
to
VBA Code:
.Offset(0)
so that it included the headings from the autofilter when copied over (just an FYI if anyone else wanted to know!)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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)...

VBA Code:
 .Offset(1).Resize(.Rows.Count - 1, 11).SpecialCells(12).Copy Sheets(mycell.Value).Range("A1")
to
VBA Code:
.Resize(, 11).SpecialCells(12).Copy Sheets(mycell.Value).Range("A1")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top