I am super new to macros and this is my second macro written. I need help as it work well in the first time I run but crashed starting from the second time.
' Fliterme2 Macro
' Fliter 2
' Keyboard Shortcut: Ctrl+Shift+G
Sheet3.Range("A2:BB10094").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
I am using this type of macro to filter data of on my 4 worksheets(I have 4 macro for filtering), based on the criteria in my another sheet(I have made a dropdown list). This work pretty well.
Dim wbOrig As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim wsTemp As Worksheet
Dim varFilename As Variant
Application.ScreenUpdating = False
Set wbOrig = ActiveWorkbook
Set wbNew = Workbooks.Add(1)
wbNew.Worksheets(1).Name = "Temp"
Set wsTemp = wbNew.Worksheets("Temp")
For Each ws In wbOrig.Worksheets
If ws.Visible = True Then
' Add a new worksheet at the end of the new workbook and name it after the current worksheet from the original workbook
wbNew.Worksheets.Add(after:=wbNew.Worksheets(wbNew.Worksheets.Count)).Name = ws.Name
' Copy the current worksheet from the original workbook to the worksheet in the new workbook with the same sheet name
' Delete the 'Temp' worksheet from the new workbook
Application.DisplayAlerts = False
Application.CutCopyMode = False
Application.DisplayAlerts = True
varFilename = wbOrig.Worksheets("Summary").Range("C3").Value & " Site Report" & ".xlsm"
.Saveas Filename:=varFilename, FileFormat:=xlWorkbookNormal
' Turn on screen updating
Application.ScreenUpdating = True
Then I use the above macro to save it as a new named workbook. It will work the first time but it will crashed at paste special or the .saveas when I change the criteria, re-filter and save the second new workbook.
I copied these macro online and amended them myself. Can anyone be able to tell me why this happening?
Many many thanks.