Subscript out of range error copying worksheet to new workbook

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,355
Office Version
365, 2016
Platform
Windows
I need some help ...

Rich (BB code):
Fpath = "U:\PWS\Parks\Parks Operations\Sports\WATSOP19\STAFF\BackupData\"
fname = "staff_schedule_BU (" & Format(Now, "dd-mm-yy hh,mm,ss") & ").xlsm"
Set newbook = Workbooks.Add
wb_pstaff.Worksheets("STAFF_SCHEDULE").Copy after:=newbook.Worksheets("Sheet1")
If Dir(Fpath & fname) <> "" Then
    MsgBox "File " & Fpath & fname & " already exists.", vbExclamation, "FILE ERROR"
Else
    newbook.SaveAs filename:=Fpath & fname
    Set wb_staffschedulebu = Workbooks(fname)
    MsgBox "Backup : " & fname & " secured.", vbInformation, "SUCCESS"
End If
wb_staffschedulebu.Close
I am receiving a "Subscript out of range" error with the line in red.

Worksheet "STAFF_SCHEDULE" is found in workbook alias wb_pstaff (which is recognized properly) and I wish to copy it to a newly created workbook (newbook) after "Sheet1"

Thoughts?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,317
Office Version
2013
Platform
Windows
I can't test......but I think you need

Code:
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & ".xlsm"
Set newbook=activeworkbook
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,355
Office Version
365, 2016
Platform
Windows
Thank you Michael, I'm not sure if your suggestion would be a solution. I'm not having any problem creating or saving the new workbook, just copying a worksheet from one book to the other.

Much appreciate the effort!
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,355
Office Version
365, 2016
Platform
Windows
It must be getting late ... such a foolish error.
Its all good friends. Helps if I use the correct workbook.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,317
Office Version
2013
Platform
Windows
:LOL::LOL:....doesn't have to be late for me to make those !!!
 

Forum statistics

Threads
1,085,841
Messages
5,386,278
Members
401,991
Latest member
jimmywhitetrix

Some videos you may like

This Week's Hot Topics

Top