Save only 9 of 18 sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Thank you Ron. I will give it a try

Ron de Bruin said:
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
Click to expand...
 
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
 
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
 
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
 
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
 
Use this then

.SaveAs "C:\My Documents\Audits\" _
& Sheets("CASH").Range("C5").Value _
& " " & Format(Now, "dd-mm-yy h-mm-ss") _
& ".xls"
 
putting the year before the month before the day of month
would look a lot better in directory.
 
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
 
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
 
Back
Top