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.
[VBA]
Sub Filterme2()
'
' Fliterme2 Macro
' Fliter 2
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Sheet3.Range("A2:BB10094").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criteria1"), Unique:=False
End Sub[/VBA]
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.
[VBA]
Sub Saveas()
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
ws.Cells.Copy
wbNew.Worksheets(ws.Name).Cells.PasteSpecial xlPasteValues
wbNew.Worksheets(ws.Name).Cells.PasteSpecial xlPasteFormats
ActiveSheet.Cells(1, 1).Select
End If
Next ws
' Delete the 'Temp' worksheet from the new workbook
Application.DisplayAlerts = False
wsTemp.Delete
Application.CutCopyMode = False
Application.DisplayAlerts = True
With wbNew
varFilename = wbOrig.Worksheets("Summary").Range("C3").Value & " Site Report" & ".xlsm"
.Saveas Filename:=varFilename, FileFormat:=xlWorkbookNormal
End With
' Turn on screen updating
Application.ScreenUpdating = True
End Sub[/VBA]
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.