pjmsimmons
Board Regular
- Joined
- Dec 13, 2011
- Messages
- 80
Hi All,
I am having a little trouble with the following code. It keeps failing at this line:
wbNew.SaveAs Filename:=savefilename, FileFormat:=6
can anyone help?
regards,
Paul
I am having a little trouble with the following code. It keeps failing at this line:
wbNew.SaveAs Filename:=savefilename, FileFormat:=6
can anyone help?
regards,
Paul
VBA Code:
Set wb = ThisWorkbook 'Name of the workbook you are copying from
Set ws = wb.Sheets("Sample Sheet1") 'Name of sheet you are copying
'adds New Workbook
Set wbNew = Workbooks.Add
wb.Activate
'copies sheet to new workbook
wb.Sheets("Sample Sheet1").Copy Before:=wbNew.Sheets(1)
With wbNew.Sheets("Sample Sheet1").UsedRange
.Value = .Value
'converts formulas to values
End With
'Prints out Assignment sheet
wb.Worksheets("Assignment Sheet1").PrintOut
'Save as dialog box to save as CSV file
savefilename = "R:\SAMPLE SHEETS\2020\" & wbNew.Worksheets("Sample Sheet1").Range("F16").Value & ".csv"
wbNew.SaveAs Filename:=savefilename, FileFormat:=6
wbNew.Close
MsgBox "New Run Sheet Created and Saved As" & savefilename