Save Workbook Into A Folder with NewName

cychua · Jan 10, 2015

Good day to all of the Guru here.

I got empty knowledge for VBA. But i used to copy and paste, modify whatever i knew to suit my needs.
My very 1st workbook works like a charm for me.

But when i try to work out on new workbook, it just doesnt seem right and it keeps showing error on this phrase,

Code:

[ATTACH]3038._xfImport[/ATTACH]

Any help would be much appreciated.

Thanks!

Tinbendr · Jan 17, 2015

Welcome to the board!

Seems you had a little trouble posting the error message.

This seems to work.

Code:

Sub AirScreening()
    Dim NewFN As Variant
    Dim ClientCode As String
    Dim JobNumClass As String
    Dim JobNum As String
    Dim JobDate As String
    Dim Mypath As String
    
    ' Copy Air Screening to a new workbook
    Sheets(Array("LIST", "NAME", "QUOTE")).Copy
    ActiveSheet.Shapes.Range(Array("AIRBUTTON")).Delete
    ClientCode = Sheets(1).Range("c2").Text
    JobNumClass = Sheets(1).Range("c1").Text
    JobNum = Sheets(1).Range("d1").Value
    JobDate = Format(Sheets(1).Range("J2").Value, "mm-dd-yy")
    Mypath = "\\CYCHUA-PC\Users\Public\Documents\Client AIR Bill\"
    NewFN = Mypath & ClientCode & JobNumClass & JobNum & " " & JobDate & ".xlsm"
    Debug.Print NewFN
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close
    NEXTAirScreening
End Sub

Bob Phillips · Jan 17, 2015

Isn't NEXTAirScreening a problem, it writes to a range which defaults to the Activeworkbook, the previous routine closes the Activeworkbook - potential conflict.

Save Workbook Into A Folder with NewName

cychua

New member

Tinbendr

New member

Bob Phillips

Super Moderator