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

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

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