Code does as instructed, but creates and saves a Book1

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
 
D

Dave Peterson

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.
 
P

Pierre

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top