Code does as instructed, but creates and saves a Book1

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

Am using this code to save the active sheet in a new book called
"Monthly_Inventory" in compatability mode. It works fine, but it also
adds another workbook to the directory. .Book1, or Book2, etc. Can
anyone look at the code and determine which line is causing this
additional workbook to be created?
Many thanks in advance.
Pierre

Sub Create_New_Workbook_Inventory()
'Save with formatting Macro
'
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Copy
Set newbk = ActiveWorkbook
newbk.ActiveSheet.Cells.Copy
newbk.ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

InitialName = ("Monthly_Inventory") & ".xls"
FName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel Files (*.xls), *.xls")

ActiveWorkbook.SaveAs , FileFormat:=xlExcel8
newbk.SaveAs Filename:=FName

End Sub
 
You have a pair of lines here:

ActiveWorkbook.SaveAs , FileFormat:=xlExcel8
newbk.SaveAs Filename:=FName


The top one doesn't specify the file name, so excel will use what it likes
(book1, book2, ...)

Maybe deleting the first line and using:

newbk.SaveAs Filename:=FName, fileformat:=xlexcel8

would work ok.
 
Dave, many thanks, yet again.

Pierre

You have a pair of lines here:

ActiveWorkbook.SaveAs , FileFormat:=xlExcel8
newbk.SaveAs Filename:=FName

The top one doesn't specify the file name, so excel will use what it likes
(book1, book2, ...)

Maybe deleting the first line and using:

newbk.SaveAs Filename:=FName, fileformat:=xlexcel8

would work ok.
Click to expand...
 
Back
Top