Stop a workbook from Opening
 

Hi all

When I run this macro it opens the workbook I've just saved, how can I stop
it from opening after the macro has ran.

Sub SaveOneSheet()
Dim Sht As Worksheet
Const PATH As String = "C:\test\"
Set Sht = ActiveWorkbook.Sheets("Test1")
Sht.Select
Sht.Copy
ActiveWorkbook.SaveAs Filename:= _
PATH & Sht.Range("G1") & ".xls", FileFormat:=xlNormal
End Sub

Thanks in advance

Dave

Stop a workbook from Opening
 

Dave, if you're saying that you only want to save the file once as a
certain name (depending on the value of G1), then you could check to
see whether a file by that name already exists using this function
(from Walkenbach):

Function FileExists(fname) as Boolean
FileExists=Dir(fname)<""
End Function

Sub SaveOneSheet()
Dim Sht As Worksheet
Dim daFile as string
Const PATH As String = "C:\test\"
daFile=PATH & activeworkbook.sheets("Test1").range("G1").value
If Not FileExists(daFile) then
Set Sht = ActiveWorkbook.Sheets("Test1")
? ? Sht.Select
? ? Sht.Copy
? ? ActiveWorkbook.SaveAs Filename:= _
? ? ? ? PATH & Sht.Range("G1") & ".xls", FileFormat:=xlNormal
End If
End Sub

I haven't tested this, but it should work. Of course, if G1's value
changes, then the sheet WILL be saved under the new name.
HTH, James

Stop a workbook from Opening
 

Thanks Zone, All the best


"Zone" wrote in message
ps.com...
Dave, if you're saying that you only want to save the file once as a
certain name (depending on the value of G1), then you could check to
see whether a file by that name already exists using this function
(from Walkenbach):

Function FileExists(fname) as Boolean
FileExists=Dir(fname)<""
End Function

Sub SaveOneSheet()
Dim Sht As Worksheet
Dim daFile as string
Const PATH As String = "C:\test\"
daFile=PATH & activeworkbook.sheets("Test1").range("G1").value
If Not FileExists(daFile) then
Set Sht = ActiveWorkbook.Sheets("Test1")
? ? Sht.Select
? ? Sht.Copy
? ? ActiveWorkbook.SaveAs Filename:= _
? ? ? ? PATH & Sht.Range("G1") & ".xls", FileFormat:=xlNormal
End If
End Sub

I haven't tested this, but it should work. Of course, if G1's value
changes, then the sheet WILL be saved under the new name.
HTH, James