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!
Thanks in advance!
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!