how do i save a single sheet from my workbook?

[eyesonly1965]

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_filename)
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 ?

[duane]

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.

[Alex]

'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 Before:=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_filename)
> 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
>
>

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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_filename)
> 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
>

[eyesonly1965]

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 Before:=newWB.Worksheets(1)
ThisWorkbook.Worksheets("Gegevensblad").Copy Before:=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.

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
> Before:=newWB.Worksheets(1)
> ThisWorkbook.Worksheets("Gegevensblad").Copy
> Before:=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
>