Save PO or Invoice to another file, clear contents and create a new blank form

[CBelli]

I am trying to save a PO to another folder on our local network then clearing the information and automatically generating another invoice number and new sheet to write a new PO: Here is the VBA language I was using, which was working but now is not working and I keep getting an error 1004 SaveAs of object Workbook Failed. Any help would be appreciated. Thank you.

Sub SavePOWithNewName()
Dim NewFN As Variant
' Copy Purchase Order to new workbook
ActiveSheet.Copy
NewFN = "T:\Winemaking\Oak Alternatives\2020 Oak Alternatives\2020 Purchase Orders\FCC20-" & Range("F5").Value & Range("B10").Text & ".PDF"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

 

[DanteAmor]

Hi and welcome to MrExcel!

I keep getting an error 1004 SaveAs

What exact data do you have in cells F5 and B10?
Do you have a date in any of these cells?

& ".PDF"

There should be ".xlsx":

NewFN = "T:\Winemaking\Oak Alternatives\2020 Oak Alternatives\2020 Purchase Orders\FCC20-" & Range("F5").Value & Range("B10").Text & ".xlsx"

 

[CBelli]

Hi and welcome to MrExcel!



What exact data do you have in cells F5 and B10?
Do you have a date in any of these cells?

& ".PDF"

There should be ".xlsx":

NewFN = "T:\Winemaking\Oak Alternatives\2020 Oak Alternatives\2020 Purchase Orders\FCC20-" & Range("F5").Value & Range("B10").Text & ".xlsx"

Hi and thank you for the message F5 has the PO number that automatically changes to the next number when saved and closed and B10 has a vendor name. Thank you I changed the file back to xlsx.

 

[DanteAmor]

You can give an example of what you have in each cell (F5 and B10).
You still have the problem.
Check that the folder path is well written.

 

[CBelli]

The folder path is: NewFN = "T:\Winemaking\Oak Alternatives\2020 Oak Alternatives\2020 Purchase Orders\FCC20-" & Range("F5").Value & Range("B10").Text & ".xlsx"

I'm not sure how to change it, because it was working just fine, saving to the correct file, etc.

 

[DanteAmor]

You can give an example of what you have in each cell (F5 and B10).

Please provide the data you have in the cells when the error occurs.

 

[CBelli]

in F5 - data is a number , i.e. 505
in B10 - data is a vendor name, i.e. Stavin

does this help?

 

[DanteAmor]

The macro works for me.
After changing "pdf", now what does the error message say?

 

[CBelli]

I continue to get the same error message. when I remove the Range("B10").text of the macro it works. But I would like to give the PO a name as well. Thanks for all your help.

 

[DanteAmor]

in B10 - data is a vendor name, i.e. Stavin

Is that a real example?

Something more particular about cell B10, is it merged, does it have other characters?

Change
Range("B10").text

To
Range("B10").Value