Hiya I am using a macro to create a workbook in another file. I run it
from one file and then it opens up the other file. But then I want it
to save and close the file im creating the new table in.

I am struggling with what I need to do for this.

I can make it close both files but I want to keep the original file
open and save and close the 2nd file.

Any ideas :)

Sub new_workbook()
Dim ws As Worksheet
Dim newSheetName As String
newSheetName = Sheets(1).Range("D3")
Workbooks.Open FileName:= _
"\\dir\\Test\All Advisors.xls"
For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid",
vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
..Move after:=Worksheets(Worksheets.Count)
..Name = newSheetName
End With
Workbooks.Close
End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

try
activeworkbook.close

"colic" wrote in message
...
Hiya I am using a macro to create a workbook in another file. I run it
from one file and then it opens up the other file. But then I want it
to save and close the file im creating the new table in.

I am struggling with what I need to do for this.

I can make it close both files but I want to keep the original file
open and save and close the 2nd file.

Any ideas :)

Sub new_workbook()
Dim ws As Worksheet
Dim newSheetName As String
newSheetName = Sheets(1).Range("D3")
Workbooks.Open FileName:= _
"\\dir\\Test\All Advisors.xls"
For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid",
vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
Move after:=Worksheets(Worksheets.Count)
Name = newSheetName
End With
Workbooks.Close
End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

colic,

Activeworkbook.Close
wil close whatever workbook is active at the time.

John

colic wrote:

Hiya I am using a macro to create a workbook in another file. I run it
from one file and then it opens up the other file. But then I want it
to save and close the file im creating the new table in.

I am struggling with what I need to do for this.

I can make it close both files but I want to keep the original file
open and save and close the 2nd file.

Any ideas :)

Sub new_workbook()
Dim ws As Worksheet
Dim newSheetName As String
newSheetName = Sheets(1).Range("D3")
Workbooks.Open FileName:= _
"\\dir\\Test\All Advisors.xls"
For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid",
vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
Move after:=Worksheets(Worksheets.Count)
Name = newSheetName
End With
Workbooks.Close
End Sub

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/