Advanced Fliter and .saveas not working second time(multiple worksheet)

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!