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.

On 07/15/2010 15:05, Pierre wrote:
> 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
>

--
Dave Peterson

Dave, many thanks, yet again.

Pierre

On Jul 15, 5:04*pm, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> 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.
>
> On 07/15/2010 15:05, Pierre wrote:
>
>
>
>
>
> > 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
>
> --
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -