Solved: Save only one sheet

[Gert Jan]

Hi everyone
I have a userform from wich the data, entered by users is stored in sheet(1).
I would like to have a commandbutton on the form, to copy only sheet(1) in a new workbbook
and save this workbook with just this one sheet to a path specified in sheet(2) cell A2.
The name of the saved workbook has to be a combination of the text in
sheet(1)cell C10 and sheet(1)cell B5 separated by a underscore.

Can anyone help me to get started with the code for the button please?

Gert Jan

[Bob Phillips]

[vba]

Dim sPath As String

sPath = ActiveWorkbook.Worksheets("Sheet2").Range("A2").Value
Worksheets("Sheet1").Move
With ActiveWorkbook
.SaveAs Filename = sPath & "\" & .Range("C10").Value & "_" & Range("B5").Value & ".xls"
End With
[/vba]

[Gert Jan]

Thanks for your quick resonse Bob,
when i run this i get an error, 438, this method is not supported by this object.
At first try, it said that Filename is not defined, so i've put in "Dim Filename as String", could this be causing the problem?

Gert Jan

[mdmackillop]

[VBA]Dim sPath As String

sPath = ActiveWorkbook.Worksheets("Sheet2").Range("A2").Value
Worksheets("Sheet1").Copy
With ActiveWorkbook.Sheets(1)
.SaveAs Filename:=sPath & "\" & .Range("C10") & "_" & .Range("B5") & ".xls"
End With

[/VBA]

[Gert Jan]

I already did change move into copy because the sheet has to stay in the original workbook as well.
Also, i added a worbook.close, so after saving the new workbook closes.
[vba]Dim sPath As String
sPath = ActiveWorkbook.Worksheets("Blad2").Range("A2").Value
Worksheets("Formulier").Copy
With ActiveWorkbook.Sheets(1)
.SaveAs Filename:=sPath & "\" & .Range("C10") & "_" & .Range("B5") & ".xls"
End With
ActiveWorkbook.Close
End Sub[/vba]

Bob, Malcolm, thank you both for helping

Gert Jan

[Bob Phillips]

[vba]

Dim sPath As String

sPath = ActiveWorkbook.Worksheets("Sheet2").Range("A2").Value
Worksheets("Sheet1").Move
With ActiveWorkbook
.SaveAs Filename = sPath & "\" & .Range("C10").Value & "_" & Range("B5").Value & ".xls"
End With
[/vba]

My bad. It should be

Filename:=

not

Filename =

and it needs sheet references as well as book, as in Malcolm's example.

[Gert Jan]

My bad.

I won't go tell anybody if you won't

Thanks again Bob,

Gert Jan

[Bob Phillips]

I won't go tell anybody if you won't

Thanks again Bob,

Gert Jan

But how do we silence Malcolm?

[Gert Jan]

But how do we silence Malcolm?

That would be like turning lead into gold.........impossible

[mdmackillop]

My silence can be bought for a very reasonable amount of Gold!