All I want, as with so many macros; I just want them to run on the ActiveSheet, regardless of it's name.
I can even use ActiveSheet.Name to print a popup of the actual name of a particular Workbook/sheet, so I know the Macro knows what the name of the document is, but in no way can I include it in the code unless I include the EXACT filename.
I've tried many different ideas with ActiveSheet, GetActiveSheet, OpenFileName, Sheets(ActiveSheet.Name), ThisWorkbook.ActiveSheet etc.
E.g.
With Worksheets("CurrentFileUsing")
Unless the file name is CurrentFileUsing I can't get this simple Sub to run properly.
Funny, if I just use Worksheets() it gets past that point, but then errors on the next line, seemingly expecting (Set r = Range without the period: .Range)
There's more AutoFilters but just this as an example.
I can even use ActiveSheet.Name to print a popup of the actual name of a particular Workbook/sheet, so I know the Macro knows what the name of the document is, but in no way can I include it in the code unless I include the EXACT filename.
I've tried many different ideas with ActiveSheet, GetActiveSheet, OpenFileName, Sheets(ActiveSheet.Name), ThisWorkbook.ActiveSheet etc.
E.g.
With Worksheets("CurrentFileUsing")
Unless the file name is CurrentFileUsing I can't get this simple Sub to run properly.
Funny, if I just use Worksheets() it gets past that point, but then errors on the next line, seemingly expecting (Set r = Range without the period: .Range)
Code:
Sub MoveNonStatesToSheet1()
Sheets.Add After:=Sheets(Sheets.count)
Dim r As Range, filtr As Range
With Worksheets("CurrentFileUsing")
Set r = .Range("A1").CurrentRegion
On Error Resume Next
r.AutoFilter field:=.Range("F1").Column, Criteria1:="KY"
Set filtr = r.SpecialCells(xlCellTypeVisible)
'MsgBox filtr.Address
Set filtr = r.Offset(1, 0).Resize(r.Rows.count - 0).SpecialCells(xlCellTypeVisible)
'MsgBox filtr.Address
filtr.Copy
With Worksheets("Sheet1")
.Cells(Rows.count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
filtr.EntireRow.Delete
r.AutoFilter
End Sub
There's more AutoFilters but just this as an example.
Last edited: