PDA

View Full Version : Solved: Save only one sheet



Gert Jan
12-17-2006, 06:27 AM
Hi everyone :hi:
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
12-17-2006, 07:11 AM
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

Gert Jan
12-17-2006, 07:33 AM
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
12-17-2006, 07:55 AM
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

Gert Jan
12-17-2006, 08:19 AM
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.
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

Bob, Malcolm, thank you both for helping :friends:

Gert Jan

Bob Phillips
12-17-2006, 10:36 AM
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

My bad. It should be

Filename:=

not

Filename =

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

Gert Jan
12-17-2006, 11:32 AM
My bad.

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

Thanks again Bob,

Gert Jan

Bob Phillips
12-17-2006, 11:49 AM
I won't go tell anybody if you won't

Thanks again Bob,

Gert Jan

But how do we silence Malcolm?

Gert Jan
12-17-2006, 12:20 PM
But how do we silence Malcolm?

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

mdmackillop
12-17-2006, 12:25 PM
My silence can be bought for a very reasonable amount of Gold! :2p: