camskolnick
11-20-2023, 09:36 AM
Hello everyone,
I am relatively new to VBA Programming and have been teaching myself through various forums. I wrote the code below which should save each sheet in a workbook as a separate workbook, excluding the first sheet. It seems to work, however, when one of my colleagues attempted to run the code on a workbook with ~200 sheets, it looks like it only saved 64 of the 200. Can someone let me know if there is any issue with my code? I've looked through it a few times to no avail, or is it possible that this was a case of user error?
Thanks guys! Please see the code below:
Sub Save_Sheets()
Dim i As Integer
Dim numSht As Integer
Dim ws As Worksheet
Dim fileName As String
Application.DisplayAlerts = False
fileName = Sheets(1).Cells(1, 11)
i = 2
numSht = ThisWorkbook.Worksheets.Count
Do Until i > numSht
Sheets(i).Copy
wb_name = Sheets(1).Name
If Right(fileName, 1) = "\" Then
ActiveWorkbook.SaveAs fileName:=Excel.Workbooks(fileName).Worksheets("Testing").Cells(3, 7).Value & _
Format(Date, "mm-dd-yy") & " - " & wb_name & " - Statement of Accounts.xlsx", FileFormat:=51
Else
ActiveWorkbook.SaveAs fileName:=Excel.Workbooks(fileName).Worksheets("Testing").Cells(3, 7).Value & "\" & _
Format(Date, "mm-dd-yy") & " - " & wb_name & " - Statement of Accounts.xlsx", FileFormat:=51
End If
ActiveWorkbook.Close
i = i + 1
Loop
Application.DisplayAlerts = True
End Sub
I am relatively new to VBA Programming and have been teaching myself through various forums. I wrote the code below which should save each sheet in a workbook as a separate workbook, excluding the first sheet. It seems to work, however, when one of my colleagues attempted to run the code on a workbook with ~200 sheets, it looks like it only saved 64 of the 200. Can someone let me know if there is any issue with my code? I've looked through it a few times to no avail, or is it possible that this was a case of user error?
Thanks guys! Please see the code below:
Sub Save_Sheets()
Dim i As Integer
Dim numSht As Integer
Dim ws As Worksheet
Dim fileName As String
Application.DisplayAlerts = False
fileName = Sheets(1).Cells(1, 11)
i = 2
numSht = ThisWorkbook.Worksheets.Count
Do Until i > numSht
Sheets(i).Copy
wb_name = Sheets(1).Name
If Right(fileName, 1) = "\" Then
ActiveWorkbook.SaveAs fileName:=Excel.Workbooks(fileName).Worksheets("Testing").Cells(3, 7).Value & _
Format(Date, "mm-dd-yy") & " - " & wb_name & " - Statement of Accounts.xlsx", FileFormat:=51
Else
ActiveWorkbook.SaveAs fileName:=Excel.Workbooks(fileName).Worksheets("Testing").Cells(3, 7).Value & "\" & _
Format(Date, "mm-dd-yy") & " - " & wb_name & " - Statement of Accounts.xlsx", FileFormat:=51
End If
ActiveWorkbook.Close
i = i + 1
Loop
Application.DisplayAlerts = True
End Sub