SaveAs Filename error

[pjmsimmons]

Hi All,
I am having a problem with save filename.

I am attempting to export two worksheets (Assignment Template & Exported Sheet) from a workbook to a new workbook, printout the sheets then save the workbook to a specific location. The issue is that I encounter an error on this line of code:

wbnew.SaveAs filename:=savefilename, FileFormat:=xlWorkbookNormal

but I cant see what the issue is.


The full code is entered below. Any help is appreciated. I have Windows 10 professional and office 16 installed if that's useful.

thanks for your help.

Edit: I seem to have an issue saving code tags. I know they should be there

Paul


Sub Save_Assignment_File()
Dim wb, wbnew As Workbook
Dim ws, ws1 As Worksheet
Dim filename As String
Dim X As Range
Dim Sample_Name As String
Dim Plate_ID As Range
Dim Plate_Paste As Range
Dim savefilename As String
Dim sh As Worksheet


For Each sh In ThisWorkbook.Worksheets
sh.Unprotect
Next sh


Application.ScreenUpdating = False


With Sheets("Assignment Template")
For Each X In Sheets("Assignment Template").Range("B2:B97")
X.Offset(0, 4).Copy


Sheets("Sample Details").Select
Cells.Find(What:=X, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next X
End With


Set wb = ThisWorkbook 'Name of the workbook you are copying from
Set ws = wb.Sheets("Exported Sheet") 'Name of sheet you are copying
Set ws1 = wb.Sheets("Assignment Template")
filename = ws.Range("I1") & ("Exported Sheet") & ".xlsm"
'adds New Workbook
Set wbnew = Workbooks.Add
wb.Activate


'copies sheet to new workbook
wb.Sheets("Exported Sheet").Copy Before:=wbnew.Sheets(1)
With wbnew.Sheets("Exported Sheet").UsedRange
.Value = .Value
'converts formulas to values
End With

wb.Sheets("Assignment Template").Copy Before:=wbnew.Sheets(1)
With wbnew.Sheets("Assignment Template").UsedRange
.Value = .Value
'converts formulas to values
End With


wbnew.Worksheets("Assignment Template").PrintOut
wbnew.Worksheets("Exported Sheet").PrintOut


'Save as dialog box to save as excel file
savefilename = "C:\Users\psimmons\Desktop" & wbnew.Worksheets("Assignment Template").Range("I1").Value & ".xls"
wbnew.SaveAs filename:=savefilename, FileFormat:=xlWorkbookNormal
wbnew.Close
MsgBox "New Report Created and Saved As" & savefilename


For Each sh In ThisWorkbook.Worksheets
sh.Protect
Next sh


Application.ScreenUpdating = True


End Sub

 

[shknbk2]

Does it work if you add a backslash after Desktop?

[COLOR=#333333]savefilename = "C:\Users\psimmons\Desktop\" & wbnew.Worksheets("Assignment Template").Range("I1").Value & ".xls"[/COLOR]

 

[pjmsimmons]

Hi shknbk2,
Thank you for your feedback. Unfortunately, I had spotted that particular error after I posted the code and fixed it. That is not where the error lies as it's on the next line I get an error.

Best Regards,

Paul.

 

[shknbk2]

What's the purpose of the "filename" variable?

[COLOR=#333333]Dim filename As String[/COLOR]

You don't use it anywhere except for the line that is causing the error. If you need it try changing its name. The FileName parameter of the SaveAs might be getting confused with this variable.

 

[pjmsimmons]

What's the purpose of the "filename" variable?

[COLOR=#333333]Dim filename As String[/COLOR]

You don't use it anywhere except for the line that is causing the error. If you need it try changing its name. The FileName parameter of the SaveAs might be getting confused with this variable.

Hi shknbk2,
Firstly thanks for replying to my query. Actually I have solved the issue and it wasn't a VBA error after all. This line:

[COLOR=#574123]savefilename = "C:\Users\psimmons\Desktop" & wbnew.Worksheets("Assignment Template").Range("I1").Value & ".xls"[/COLOR]

referenced a blank cell which caused the code to crash.

I would like to thank you again for trying to help me and wish you a pleasant evening.

regards,

Paul