Problem copying worksheet to new workbook with VBA

[mcomp72]

I am attempting to write code that will create a new workbook, and then copy a sheet from ThisWorkbook (sheet is named "PO Form") into that new workbook. I have not done this before using VBA, and have hit a roadblock.

Here's the simplified version of my code:

Dim NewWBname As String
Dim POwb As Workbook

NewWBname = Application.GetSaveAsFilename( _
FileFilter:="XLSX files, *.xlsx", _
Title:="Export POs as Excel file...")

Set POwb = Workbooks.Add
POwb.SaveAs NewWBname

ThisWorkbook.Activate
    
Sheets("PO Form").Select
Sheets("PO Form").Copy After:=Workbooks(NewWBname).Sheets(1)

On the last line, I am getting Run-time error 9: subscript out of range. I searched around online, and I've seen various forums that indicate this is the proper syntax for copying a sheet to a new workbook. But obviously I am misunderstanding something. Any help would be appreciated!

 

[Fluff]

Try

Dim NewWBname As String

NewWBname = Application.GetSaveAsFilename( _
   FileFilter:="XLSX files, *.xlsx", _
   Title:="Export POs as Excel file...")
Sheets("Pcode").Copy

ActiveWorkbook.SaveAs NewWBname, 51

 

[mcomp72]

My code is actually going to potentially copy the sheet several times, depending on the circumstances, so I have a FOR NEXT loop to determine how many times. And the line to copy the sheet is actually well below the line to get the new file name. In the code in between the two, I have to activate ThisWorkbook because of what the code needs to do. So I would like to use the Copy line like the way I had it, but I don't understand why it isn't working as written.

 

[Fluff]

Its because NewWBname includes the full path, rather than just the file name.
Try

Dim NewWBname As String
Dim POwb As Workbook

NewWBname = Application.GetSaveAsFilename( _
FileFilter:="XLSX files, *.xlsx", _
Title:="Export POs as Excel file...")

Set POwb = Workbooks.Add
POwb.SaveAs NewWBname

ThisWorkbook.Activate
    
Sheets("PO Form").Copy After:=POwb.Sheets(1)

 

[mcomp72]

AHHH! Yep, that fixed it. Thank you!!

 

[Fluff]

You're welcome