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

CBelli

New Member
Joined
Feb 11, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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
 
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
in F5 - data is a number , i.e. 505
in B10 - data is a vendor name, i.e. Stavin

does this help?
 
Upvote 0
The macro works for me.
After changing "pdf", now what does the error message say?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top