PDA

View Full Version : Solved: Button to save selected sheets to new workbook



jammer6_9
08-17-2007, 11:52 PM
I have 10 sheets in a workbook. What I wanted to do is have 1 commandbutton to copy sheet1,sheet2,sheet3 then save it as new workbook. Pls :help

jammer6_9
08-18-2007, 12:41 AM
Code below works BUT how can I copy sheets as values only without formula?


Private Sub cmdOK_Click()
Dim wks As Worksheet
Dim strSheet() As String
Dim strNewWB As String
Dim strpath As String
Dim blnFound As Boolean
Dim intWS As Integer
Dim i As Integer

strpath = Application.DefaultFilePath


For i = 0 To lstDept.ListCount - 1
If lstDept.Selected(i) = False Then
ReDim Preserve strSheet(0 To intWS) As String
strSheet(intWS) = lstDept.List(i)

intWS = intWS + 1

Else
If strNewWB = "" Then
strNewWB = lstDept.List(i)
End If
End If
Next i

If UBound(strSheet) = lstDept.ListCount - 1 Then
MsgBox "Please select one or more departments", vbInformation, _
"No Selection Made"
Else
ActiveWorkbook.SaveAs strpath & "\" & strNewWB & ".xls"

Application.DisplayAlerts = False
Sheets(strSheet()).Delete
Application.DisplayAlerts = True

ActiveWorkbook.Save

Unload Me
End If
End Sub


Private Sub UserForm_Initialize()
Dim wks As Worksheet


For Each wks In ActiveWorkbook.Worksheets
lstDept.AddItem wks.Name
Next wks
End Sub

YellowLabPro
08-18-2007, 02:20 AM
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Cells.Value

daniel_d_n_r
08-18-2007, 02:32 AM
Once you have your new workbooks with the 3 copied sheets in it run this code over each copy,it seems to work here and removes the formulas, simply by selecting the sheet copying it and pasting it over the sheet again as a past special values only.

a bit messy but simple enough, it may help

cheers


Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

jammer6_9
08-18-2007, 02:39 AM
So where can I place this code now?



ActiveSheet.UsedRange = ActiveSheet.UsedRange.Cells.Value

Bob Phillips
08-18-2007, 02:40 AM
An alternative way



ActiveWorkbook.SaveCopyAs "C:\Test\new file.xls"
Set wb = Workbooks.Open("new file.xls")
Application.DisplayAlerts = False
wb.Sheets(Array("Sheet10", "Sheet9", "Sheet8", "Sheet7", "Sheet6", "Sheet5", "Sheet4")). _
Delete
wb.Save
Application.DisplayAlerts = True

YellowLabPro
08-18-2007, 02:44 AM
Try it here: But do it on a test copy. As I am not sure where this Private Sub Resides. You basically need to place it right after where your code copies the sheet to the other workbook and that sheet is still active.



Private Sub UserForm_Initialize()
Dim wks As Worksheet


For Each wks In ActiveWorkbook.Worksheets
lstDept.AddItem wks.Name
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Cells.Value
Next wks
End Sub

YellowLabPro
08-18-2007, 02:49 AM
xld-
Falselse?
One detail- I don't see in your code where it will return the sheets w/ values instead of formulae.
That is an elegant way to make a copy, nice..... :-)

Bob Phillips
08-18-2007, 02:54 AM
Missed that bit Doug, it was in the follow-up



ActiveWorkbook.SaveCopyAs "C:\Test\new file.xls"
Set WB = Workbooks.Open("new file.xls")
Application.DisplayAlerts = False
WB.Sheets(Array("Sheet10", "Sheet9", "Sheet8", "Sheet7", "Sheet6", "Sheet5", "Sheet4")). _
Delete
For Each sh In WB
sh.Cells.Value = sh.Cells.Value
Next sh
WB.Save
Application.DisplayAlerts = True

YellowLabPro
08-18-2007, 03:49 AM
jammer- there you have it.... xld's code cuts out any un-needed steps.

If you do try mine, let me know if it worked. I know the one line works, just not sure where your code was looping to copy the sheets to the new workbook, and this is where I would drop in the line I gave you. I opted to use UsedRange.Cells as this will not process the entire sheet which will take longer to go through every cell. But UsedRange has some quirks I have read and so you might experience some results you may not desire. So test both ways..... xld's ensure's against this since it processes all the cells.

YellowLabPro
08-18-2007, 03:51 AM
Bob,
Legal?
WB.Sheets Not in (Array("Sheet1", "Sheet2", "Sheet3")).Delete

Bob Phillips
08-18-2007, 03:55 AM
Afraid not, you would have to loope and MATCH them.

jammer6_9
08-18-2007, 05:34 AM
This is a quick alternative BUT please note that sheet1 is looking up values to one of this sheet that will be deleted so once the sheet is gone, sheet1 value will turn to #REF!...


Missed that bit Doug, it was in the follow-up



ActiveWorkbook.SaveCopyAs "C:\Test\new file.xls"
Set WB = Workbooks.Open("new file.xls")
Application.DisplayAlerts = False
WB.Sheets(Array("Sheet10", "Sheet9", "Sheet8", "Sheet7", "Sheet6", "Sheet5", "Sheet4")). _
Delete
For Each sh In WB
sh.Cells.Value = sh.Cells.Value
Next sh
WB.Save
Application.DisplayAlerts = True

jammer6_9
08-18-2007, 06:05 AM
This simple code below answers my query... Thanks to all response...


Sheets(Array("Comments", "Audit Summary", "Summary", "Deduction")).Copy
Set wb = ActiveWorkbook
wb.SaveAs ThisWorkbook.Path & "\NewWorkbook.xls"

Bob Phillips
08-18-2007, 06:50 AM
What about values witjout formulae?

jammer6_9
08-18-2007, 07:53 AM
Well that remain unsolved but I can go on with this code for now. My Boss really wanted me to finished an output ASAP. If you got any solution "sure you have" : pray2: i will appreciate it...


What about values witjout formulae?

YellowLabPro
08-18-2007, 07:57 AM
jammer,
You have a new workbook w/ the desired sheets in this workbook now?
These sheets are the sheets listed in your array-
"Comments", "Audit Summary", "Summary", "Deduction" ?

jammer6_9
08-18-2007, 08:05 AM
Yes I do have as I desired. The only thing now is those sheets listed in my array still have formulas link to the OLD Workbook wherein during opening the newworkbook, it still ask the user if they want to update links or not which I wanted to eliminate.

Bob Phillips
08-18-2007, 08:19 AM
Just tag that bit of code that I added after Doug's comment onto your cde.

YellowLabPro
08-18-2007, 08:22 AM
Just to fully understand-
Which sheet contains the formulas?

jammer6_9
08-18-2007, 10:42 PM
These sheets are the sheets that contains formulas
"Comments", "Audit Summary", "Summary", "Deduction"


Just to fully understand-
Which sheet contains the formulas?

Bob Phillips
08-19-2007, 02:09 AM
I don't think the names of the sheets matter



Sheets(Array("Comments", "Audit Summary", "Summary", "Deduction")).Copy
Set wb = ActiveWorkbook
wb.SaveAs ThisWorkbook.Path & "\NewWorkbook.xls"
For Each sh In WB
sh.Cells.Value = sh.Cells.Value
Next sh
WB.Save

jammer6_9
08-19-2007, 03:02 AM
Perfect xld... This solved & ended my query...


I don't think the names of the sheets matter



Sheets(Array("Comments", "Audit Summary", "Summary", "Deduction")).Copy
Set wb = ActiveWorkbook
wb.SaveAs ThisWorkbook.Path & "\NewWorkbook.xls"
For Each sh In WB
sh.Cells.Value = sh.Cells.Value
Next sh
WB.Save

YellowLabPro
08-19-2007, 03:56 AM
The reason I ask is to find out where jammer's problem arose when he converted the formulas to values.


This is a quick alternative BUT please note that sheet1 is looking up values to one of this sheet that will be deleted so once the sheet is gone, sheet1 value will turn to #REF!..

jammer, if you are happy w/ your results I will cease in my inquiries. If you however still desire to convert the formulae to values, then I need to know if you really have any sheets that are retrieving their values from a referenced sheet.
You stated that, "that sheet1 is looking up values to one of this sheet ", one of this sheet is ambiguous.
If you delete the sheet containing formula-sheet first, then yes your values will error. You need to convert your formula sheet first then delete this other sheet.
If this gets too confusing you might consider posting the two books.

geekgirlau
08-19-2007, 10:49 PM
Doug, I think you've answered the question right there:

... convert formulas to values THEN delete the sheets.

jammer6_9
08-20-2007, 03:00 AM
Tnx,tnx,tnx for your replies. You actually get & understand what I wanted to do. Although I was able to copy Sheets I desired, Formulas were still there and during opening of the NewFile, the user is asked if the workbook needs update the links...

The code below copy the values of source sheet (ActiveWorkbook) to new sheet (NewWorkbook) although the formula & link were still there in the new sheet of the new workbook... My query was is it possible to copy sheet(ActiveWorkbook) values only to a new sheet(NewWorkbook)? If Yes how? :help

Sheets(Array("Comments", "Audit Summary", "Summary", "Deduction")).Copy
Set wb = ActiveWorkbook
wb.SaveAs ThisWorkbook.Path & "\NewWorkbook.xls"
For Each sh In WB
sh.Cells.Value = sh.Cells.Value
Next sh
WB.Save


The reason I ask is to find out where jammer's problem arose when he converted the formulas to values.

jammer, if you are happy w/ your results I will cease in my inquiries. If you however still desire to convert the formulae to values, then I need to know if you really have any sheets that are retrieving their values from a referenced sheet.
You stated that, "that sheet1 is looking up values to one of this sheet ", one of this sheet is ambiguous.
If you delete the sheet containing formula-sheet first, then yes your values will error. You need to convert your formula sheet first then delete this other sheet.
If this gets too confusing you might consider posting the two books.

YellowLabPro
08-20-2007, 03:30 AM
jammer,

The code below copy the values of source sheet (ActiveWorkbook) to new sheet (NewWorkbook)
Actually it does not copy the values, it is copying the sheets to the new workbook. Since it is copying the sheets it has a reference to the formulas in the originating workbook, that is why I believe you are being asked about updating the links.
I am sure xld has an answer for this, but until he pops in, we can knock this around. If I could see the sheets I could better assist, if not then I will not be able to setup a test environment and test for a few days. But I am quite sure this will be a fairly simple solve.

edit: If you only want to copy the values to the new workbook, you would need to convert the formulae to values prior to the sheet copy, which I don't think you will want to do.