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

 

[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

 

[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