VBA Help: SaveAs window and file creation not working

[nutrageousd]

Hi All,

I have code that I am using to copy some sheets from an existing workbook into its own seperate workbook. I have a few problems with it that I was hoping someone could help me clear up. (Forgive me I'm still new to this!) Any help is appreciated. My Problems are as follows:

1. Once the Save As window pops up, the 'Cancel' button doesn't work. The user is forced to save the file.

2. Once the sheets are copied over to the new workbook, the new workbook remains open. I would like it to be saved, but not opened.

3. After the new workbook is created with the copied worksheets, when the user proceeds to close the workbook, it asks the user to save again. I'm guessing the sheets copied to the new file after it was created? I would like the user to not have to do this.


Any help provided is greatly appreciated. Thank you


Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim fName
'Set Default File Path to Save to
ChDir "C:\Documents and Settings\My Documents"
With Application
.DisplayAlerts = False
.ScreenUpdating = False
<o> </o>
Set wb = Workbooks.Add
<o> </o>
ThisWorkbook.Worksheets(Array("Summary", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8")).Copy before:=Worksheets("Sheet1")
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("A1").Value = "" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Do
fName = Application.GetSaveAsFilename _
(ThisFile, filefilter:="Excel Files, *.xlsx")
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName
Next ws
.DisplayAlerts = True
.ScreenUpdating = True
End With
Set wb = Nothing
Set ws = Nothing
End Sub

 

[BrianB]

You have made it impossible to use the cancel button. When it is clicked it makes fname = False.
Your code will keep looping until some sort of entry is made.

To be more certain of getting an answer you will need to format any code in the message correctly - with indents etc. If there had been any more code here I would not have bothered trying to read it. Luckily too it is a simple error to find. Perhaps if you had indented your code correctly you would have seen the error.

Do
    fName = Application.GetSaveAsFilename _
    (ThisFile, filefilter:="Excel Files, *.xlsx")
Loop Until fName <> False

 

[nutrageousd]

Sorry for the big mess, but thank you so much for the help!