SaveAs method not actually saving, wrong sheets being deleted (1004 Error)

[rossross]

Option Explicit

Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim r As Long
Dim i As Long
Dim er As Long
Dim c As Range
Dim sht As Object
Dim wb2 As Workbook
Dim fname As String
Dim fpath As String


Sub printshts()

Set wb = ThisWorkbook
Set ws = wb.Worksheets("List")
Set ws2 = wb.Worksheets("Amount")

With ws2
    er = Cells(Rows.Count, "A").End(xlUp).Row
    .Range("A2:T" & er).Sort key1:=.Range("B2"), order1:=xlAscending, Header:=xlYes, _
        ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
End With

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

With ws
    Set rng = .Range("A2:A101")
    For Each c In rng
        If c = 0 Then
        'do nothing
        Else
        ws2.Copy after:=Sheets(Sheets.Count)
        With ActiveSheet
            ActiveSheet.name = c.Value2
            i = 2
            er = Cells(Rows.Count, "A").End(xlUp).Row
            For r = er To 3 Step -1
                If .Cells(r, i) <> ActiveSheet.name Then
                    .Cells(r, i).EntireRow.Delete
                End If
                .Range("B3").Select
            Next r
            
            fpath = "C:\Users\User\Desktop\Folder\Folder2\"
            fname = ThisWorkbook.ActiveSheet.Range("B3") & ".xlsx"
    
            Set wb2 = Workbooks.Add
            wb.Activate
            wb.ActiveSheet.Copy before:=wb2.Sheets(1)
            wb2.Sheets("Sheet1").Delete
            wb2.SaveAs Filename:=fname
            wb2.Close
            
        End With
        End If
    Next
End With

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True


MsgBox "Completed", vbOKOnly

Call delshts

End Sub


Sub delshts()

Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If ws.name <> "Amount" Or ws.name <> "List" Then
    ws.Delete
    End If
'Select Case ws.name
'    Case "Amount", "List"
'        'do nothing
'    Case Else
'        ws.Delete
'End Select
Next ws
Application.DisplayAlerts = True
        
End Sub

What i'm attempting to do is replicate one sheet, remove everything that isn't supposed to be there, save that on a new ws and then as a new workbook with the file name. The first routine printshts executed perfectly as intended ONCE then hasn't worked again since. I'm not sure why.

Everything executes fine until it comes time to save the file. 1) It's still displaying the Save screen when i wouldn't think that it should. Then the file goes nowhere when it should land in this folder. 2) I keep getting an error message with both methods of deleting sheets. Breakpoint at ws.delete gives me a run-time 1004 message the deletion method doesn't work. I'm looking right at the sheets and i haven't spelled anything incorrectly. PLEASE tell me what it is wrong here.

 

[rossross]

quick update: the workbook was slightly corrupted somehow and had a sheet that shouldn't have been there, but snuck in. switched to a new workbook and now the sheets delete correctly. Still dealing with the file path issue however. They're all being saved to mydocuments and the savings window does keep appearing. I suspect it's because wb2 is generating and has no path and goes to my default location which is mydocs. Not sure where to make the change in here to go to the original wb's location which i have clearly specified.

 

[Alex Blakenburg]

Try
wb2.SaveAs Filename:=fpath & fname

 

[rossross]

Try
wb2.SaveAs Filename:=fpath & fname

nailed it. any particular reason why that works?

 

[Alex Blakenburg]

nailed it. any particular reason why that works?

You actually said it yourself:

suspect it's because wb2 is generating and has no path and goes to my default location which is mydocs.

fname* is just the file name with no path. You had the path stored in fpath. So the fpath & fname just adds the path on the front of the fname.

* this is just an assumption since we can't see what you have in B3