Save only 9 of 18 sheets

[Guest]

I have an "application" that is made up of worksheets which the user fills
with data, the other worksheets are for informational purposes. I need to
have a macro that will select the nine worksheets filled with data to be
saved to a filename based on a cell in one sheet and then leave the
"application" blank for the next use.
Any help would be appreciated.

 

[Ron de Bruin]

Try this

Enter all sheet names in the array

Sub test()
Sheets(Array("Sheet1", "Sheet3")).Copy
With ActiveWorkbook
.SaveAs "C:\" & Sheets("Sheet1").Range("A1").Value & ".xls"
.Close False
End With
End Sub

 

[Guest]

Thank you Ron. I will give it a try

Try this

Enter all sheet names in the array

Sub test()
Sheets(Array("Sheet1", "Sheet3")).Copy
With ActiveWorkbook
.SaveAs "C:\" & Sheets("Sheet1").Range("A1").Value & ".xls"
.Close False
End With
End Sub

 

[Guest]

Ron
Sorry, How would I make this happen with a Command Button?

 

[Ron de Bruin]

Hi Rob

View>Toolbars and click on forms
Add a button on your sheet
Select the macro name in the dialog that popup
OK

Or right click on the button and choose Assign Macro

 

[Guest]

Ron
I am getting a subscript out of range error, cursor goes to Array

 

[Ron de Bruin]

Post the macro Rob in this thread

 

[Guest]

ok here is what i have
Sub test()
Sheets(Array("AUDIT REPORT", "OIL", "STICKS", "LOTTERY NY", "LOTTERY
PA", "LOTTERY OH", "CALC END INV", "MERCHANDISE", "CIGSNY", "CIGS PA",
"CASH")).Copy
With ActiveWorkbook
.SaveAs "C:\My Documents\Audits\" & Sheets("CASH").Range("C5").Value
& ".xls"
.Close False
End With
End Sub

 

[Ron de Bruin]

Hi Rob

This is working for me

I think your problem is this :
There is a space before one or more sheet name(s)

The code can't find the sheet then

Sub test()
Sheets(Array("AUDIT REPORT", "OIL", "STICKS", "LOTTERY NY", _
"LOTTERY PA", "LOTTERY OH", "CALC END INV", "MERCHANDISE", _
"CIGSNY", "CIGS PA", "CASH")).Copy
With ActiveWorkbook
.SaveAs "C:\My Documents\Audits\" _
& Sheets("CASH").Range("C5").Value & ".xls"
.Close False
End With
End Sub

 

[Guest]

Hey Ron
I tried this again, however do I ineed a Dim statement for the array?
Thanks
Rob

 

[Ron de Bruin]

I tried this again, however do I ineed a Dim statement for the array?

No

Send me your test file (Private) with the macro in it and I take a look at it

 

[Guest]

Ron
Do you mean regular e-mail attachment to the address in your profile
Rob

 

[Ron de Bruin]

(e-mail address removed)

 

[Ron de Bruin]

There is a space after the sheet CASH Rob

"Cash"
"Cash "

 

[Guest]

how would I add the date to the filename

There is a space after the sheet CASH Rob

"Cash"
"Cash "

 

[Ron de Bruin]

Use this then

.SaveAs "C:\My Documents\Audits\" _
& Sheets("CASH").Range("C5").Value _
& " " & Format(Now, "dd-mm-yy h-mm-ss") _
& ".xls"

 

[David McRitchie]

putting the year before the month before the day of month
would look a lot better in directory.

 

[Guest]

Ron
You said there are easier ways. I have been thinking about this a realize
your probably right. I would like to have the Command Butoons open up the
individual sheets to a new workbook, so the user just picks the sheets they
need, then have that saved and printed. Any Ideas???

Rob J

 

[Ron de Bruin]

Hi Rob

You can use a userform
Add a listbox and a button on the userform
In the properties of the listbox set Multiselect to 1

Copy this code in the Userform module

Private Sub CommandButton1_Click()
Dim arr() As String
Dim N As Integer
Dim strdate As String

N = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ListBox1.List(i)
End If
Next i
If N = 0 Then
MsgBox "You must select at least one Sheet"
Exit Sub
End If
ActiveWorkbook.Worksheets(arr).Copy

strdate = Format(Now, "dd-mm-yy h-mm-ss")
With ActiveWorkbook
.SaveAs "C:\yourfilename " _
& strdate & ".xls"
.PrintOut
.Close False
End With

Unload Me
End Sub


Private Sub UserForm_Initialize()
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then
Me.ListBox1.AddItem (ws.Name)
End If
Next
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Worksheets(1).Select
End Sub

 

[Guest]

Ron
How would the userform be accessed from my application page?