i have a workbook with a few hidden sheets and a form.
but i only want to save the main sheet under a different name.

the code i have for saving is:
-Sub cmdSave_Click()

With ActiveWorkbook.Sheets("Gegevensblad")
'First part of filename...
a = Range("A4").Value & " - " & Range("B4").Value
'Filename extension...
c = ".xls"
s_filename = a + c
Dim fname As Variant
fname =
Application.GetSaveAsFilename(InitialFileName:=s_f ilename)
If fname = False Then
Debug.Print "user clicked cancel"
'do nothing
Else
Debug.Print "user chose " & fname
ActiveWorkbook.SaveAs Filename:=fname
End If
End With-

i want the option to save the file with this name but it should only
save the main sheet in a new file.
can anyone help me ?


--
eyesonly1965
------------------------------------------------------------------------
eyesonly1965's Profile: http://www.excelforum.com/member.php...o&userid=34199
View this thread: http://www.excelforum.com/showthread...hreadid=541805

you can walk throught the commands in excel by right clicking on the
sheet, select move or copy, to a new workbook, and then rename the new
workbook.


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=541805

'eyesonly1965'

I took a look at your issue. I think this code below will work for you, or
at leats you can adapt it.

This code build a filename (fname) from the ranges you designate, creates a
new workbook, adds the 'Gegevensblad' worksheet to the new workbook, and
saves the new workbook as the filename specified.

Sub SaveSheet()
Dim fname As String
Dim newWB As Workbook
Dim wb As Worksheet

fname = Range("A4").Value & " - " & Range("B4").Value & ".xls"

Set newWB = Workbooks.Add

ThisWorkbook.Worksheets("Gegevensblad").Copy Befo=newWB.Worksheets(1)

newWB.SaveAs Filename:=fname

End Sub

Is this of any use to you? Please write back if there are any problems.

Regards


Alex
"eyesonly1965" wrote:


i have a workbook with a few hidden sheets and a form.
but i only want to save the main sheet under a different name.

the code i have for saving is:
-Sub cmdSave_Click()

With ActiveWorkbook.Sheets("Gegevensblad")
'First part of filename...
a = Range("A4").Value & " - " & Range("B4").Value
'Filename extension...
c = ".xls"
s_filename = a + c
Dim fname As Variant
fname =
Application.GetSaveAsFilename(InitialFileName:=s_f ilename)
If fname = False Then
Debug.Print "user clicked cancel"
'do nothing
Else
Debug.Print "user chose " & fname
ActiveWorkbook.SaveAs Filename:=fname
End If
End With-

i want the option to save the file with this name but it should only
save the main sheet in a new file.
can anyone help me ?


--
eyesonly1965
------------------------------------------------------------------------
eyesonly1965's Profile: http://www.excelforum.com/member.php...o&userid=34199
View this thread: http://www.excelforum.com/showthread...hreadid=541805

Hi

ActiveSheet.Copy create a new workbook with your sheet

You can do it like this

Sub Copy_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"eyesonly1965" wrote in message
news:eyesonly1965.27r2ny_1147530601.8052@excelforu m-nospam.com...

i have a workbook with a few hidden sheets and a form.
but i only want to save the main sheet under a different name.

the code i have for saving is:
-Sub cmdSave_Click()

With ActiveWorkbook.Sheets("Gegevensblad")
'First part of filename...
a = Range("A4").Value & " - " & Range("B4").Value
'Filename extension...
c = ".xls"
s_filename = a + c
Dim fname As Variant
fname =
Application.GetSaveAsFilename(InitialFileName:=s_f ilename)
If fname = False Then
Debug.Print "user clicked cancel"
'do nothing
Else
Debug.Print "user chose " & fname
ActiveWorkbook.SaveAs Filename:=fname
End If
End With-

i want the option to save the file with this name but it should only
save the main sheet in a new file.
can anyone help me ?


--
eyesonly1965
------------------------------------------------------------------------
eyesonly1965's Profile: http://www.excelforum.com/member.php...o&userid=34199
View this thread: http://www.excelforum.com/showthread...hreadid=541805

i have read the code you provided and combined it with my own code.

it looks like this:

Private Sub cmdOpslaan_Click()

'variabelen declareren
Dim fname As String
Dim newWB As Workbook
Dim wb As Worksheet
'naam genereren met code voor besteller en bestelnummer
fname = Range("A4").Value & " - " & Range("B4").Value & ".xls"
'nieuwe workbook aanmaken
Set newWB = Workbooks.Add
'sheets kopieren: bestellijst en gegevensblad moeten worden
gekopieerd
'om te voorkomen dat de gegevens niet meer bestaan, dit omdat
'de orginele bestellijst leeg wordt gemaakt om weer een nieuwe te
kunnen
'maken.
ThisWorkbook.Worksheets("Bestellijst").Copy
Befo=newWB.Worksheets(1)
ThisWorkbook.Worksheets("Gegevensblad").Copy
Befo=newWB.Worksheets(2)
'nieuwe workbook opslaan onder de nieuwe naam
newWB.SaveAs Filename:=fname

End Sub

i discovered that that when i saved more new files they all showed the
same data, this because it looked in the original workbook for the data
in gegevensblad.
so i needed to copy that sheet also into the new workbook.

anyway i thank for pointing me in the right direction.:)


--
eyesonly1965
------------------------------------------------------------------------
eyesonly1965's Profile: http://www.excelforum.com/member.php...o&userid=34199
View this thread: http://www.excelforum.com/showthread...hreadid=541805

No need to add a workbook and copy the sheets in it

You can use

Sub Copy_test()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets(Array("Bestellijst", "Gegevensblad")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"eyesonly1965" wrote in message
news:eyesonly1965.27szvm_1147620301.5025@excelforu m-nospam.com...

i have read the code you provided and combined it with my own code.

it looks like this:

Private Sub cmdOpslaan_Click()

'variabelen declareren
Dim fname As String
Dim newWB As Workbook
Dim wb As Worksheet
'naam genereren met code voor besteller en bestelnummer
fname = Range("A4").Value & " - " & Range("B4").Value & ".xls"
'nieuwe workbook aanmaken
Set newWB = Workbooks.Add
'sheets kopieren: bestellijst en gegevensblad moeten worden
gekopieerd
'om te voorkomen dat de gegevens niet meer bestaan, dit omdat
'de orginele bestellijst leeg wordt gemaakt om weer een nieuwe te
kunnen
'maken.
ThisWorkbook.Worksheets("Bestellijst").Copy
Befo=newWB.Worksheets(1)
ThisWorkbook.Worksheets("Gegevensblad").Copy
Befo=newWB.Worksheets(2)
'nieuwe workbook opslaan onder de nieuwe naam
newWB.SaveAs Filename:=fname

End Sub

i discovered that that when i saved more new files they all showed the
same data, this because it looked in the original workbook for the data
in gegevensblad.
so i needed to copy that sheet also into the new workbook.

anyway i thank for pointing me in the right direction.:)


--
eyesonly1965
------------------------------------------------------------------------
eyesonly1965's Profile: http://www.excelforum.com/member.php...o&userid=34199
View this thread: http://www.excelforum.com/showthread...hreadid=541805