Save Filename
 

Hello All,

I have a workbook that has a heap of pages.
It is broken down into sheet names:
1-30 (visible)
1-30-Dock (hidden)
1-30-Delivery (hidden)
1-30-Admin (hidden)

Then continues onto the next set of four sheets, 31-60 etc.
The data from the visible sheet is copied onto the other 3 sheets.
Any sheets that are not used are deleted auto on exit, with the rest being
saved to a single file.
What I need to know is...can i save a new workbook that only contains the
visible sheets, then another with only the Dock sheets etc etc.
The number of sheets used each time varies widely. But if the visible sheet
is not used, then neither are the other 3 in the set

Confused...try being in my seat =)

Regards
Peter

Save Filename
 

You can call this with which group you want to save. for example

Option Explicit
Sub Savesheet
CopySheets ' type 1-30 sheets
CopySheets "Dock"
CopySheets "Delivery"
CopySehets "Admin"
End Sub



Sub CopySheets(Optional searchString As Variant)
Dim s As String
Dim varr As Variant
Dim sh As Worksheet
Dim shts As Worksheets
Dim icnt As Long
Dim lastrow
ReDim varr(0 To 0)
For Each sh In ActiveWorkbook.Worksheets
If IsMissing(searchString) Then
icnt = InStr(1, sh.Name, "dock", vbTextCompare)
icnt = icnt + InStr(1, sh.Name, "delivery", vbTextCompare)
icnt = icnt + InStr(1, sh.Name, "admin", vbTextCompare)


' you could change the test to visible sheets rather than
' excluding other types. -if sh.visible = xlSheetVisible then <-
' rather than -If icnt = 0 then<-
If icnt = 0 Then
lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row
If lastrow 2 Then
varr(UBound(varr)) = sh.Name
ReDim Preserve varr(0 To UBound(varr) + 1)
End If
End If
Else
If InStr(1, sh.Name, searchString, vbTextCompare) 0 Then
lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row
If lastrow 2 Then
varr(UBound(varr)) = sh.Name
ReDim Preserve varr(0 To UBound(varr) + 1)
End If
End If
End If
Next
If Not IsEmpty(varr(0)) Then
ReDim Preserve varr(0 To UBound(varr) - 1)
If IsMissing(searchString) Then
Worksheets(varr).Copy
ActiveWorkbook.SaveAs "NumberedSheets.xls"
ActiveWorkbook.Close SaveChanges:=False
Else
Set shts = Worksheets(varr)
For Each sh In shts
sh.Visible = xlVisible
Next
sh.Copy
ActiveWorkbook.SaveAs searchString & ".xls"
ActiveWorkbook.Close SaveChanges:=False
For Each sh In shts
sh.Visible = xlSheetHidden
Next
End If
End If
End Sub

I don't know what your sheets look like, so I put in a check - if there is
anything beyond row 2, then they are not empty. Adjust that check to match
your criteria.

Code is untested, so there may be typos or logic errors, but it should give
you some ideas.
--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Hello All,

I have a workbook that has a heap of pages.
It is broken down into sheet names:
1-30 (visible)
1-30-Dock (hidden)
1-30-Delivery (hidden)
1-30-Admin (hidden)

Then continues onto the next set of four sheets, 31-60 etc.
The data from the visible sheet is copied onto the other 3 sheets.
Any sheets that are not used are deleted auto on exit, with the rest being
saved to a single file.
What I need to know is...can i save a new workbook that only contains the
visible sheets, then another with only the Dock sheets etc etc.
The number of sheets used each time varies widely. But if the visible
sheet
is not used, then neither are the other 3 in the set

Confused...try being in my seat =)

Regards
Peter

Save Filename
 

Hi Peter,

try using a loop connected with the worksheet.visible
function as follows:

For Each Worksheet In Workbooks("abcd.xls")

If Worksheet.Visible = True Then
Worksheet.Copy
Workbooks("localcopyvisible.xls").Activate
Worksheet.Paste
Else
Worksheet.Copy
Workbooks("localcopyhidden.xls").Activate
Worksheet.Paste
End If

Next Worksheet


Sorry for double-Posting, missed some code ;o)

Best

Markus


-----Original Message-----
Hello All,

I have a workbook that has a heap of pages.
It is broken down into sheet names:
1-30 (visible)
1-30-Dock (hidden)
1-30-Delivery (hidden)
1-30-Admin (hidden)

Then continues onto the next set of four sheets, 31-60
etc.
The data from the visible sheet is copied onto the other
3 sheets.
Any sheets that are not used are deleted auto on exit,
with the rest being
saved to a single file.
What I need to know is...can i save a new workbook that
only contains the
visible sheets, then another with only the Dock sheets
etc etc.
The number of sheets used each time varies widely. But if
the visible sheet
is not used, then neither are the other 3 in the set

Confused...try being in my seat =)

Regards
Peter

.

Save Filename
 

Thanks Tom and Markus
I'll have a test run and see if more valium are needed, and the psych needs
a third session for this week.

Regards
Peter


"Markus Scheible" wrote:

Hi Peter,

try using a loop connected with the worksheet.visible
function as follows:

For Each Worksheet In Workbooks("abcd.xls")

If Worksheet.Visible = True Then
Worksheet.Copy
Workbooks("localcopyvisible.xls").Activate
Worksheet.Paste
Else
Worksheet.Copy
Workbooks("localcopyhidden.xls").Activate
Worksheet.Paste
End If

Next Worksheet


Sorry for double-Posting, missed some code ;o)

Best

Markus


-----Original Message-----
Hello All,

I have a workbook that has a heap of pages.
It is broken down into sheet names:
1-30 (visible)
1-30-Dock (hidden)
1-30-Delivery (hidden)
1-30-Admin (hidden)

Then continues onto the next set of four sheets, 31-60
etc.
The data from the visible sheet is copied onto the other
3 sheets.
Any sheets that are not used are deleted auto on exit,
with the rest being
saved to a single file.
What I need to know is...can i save a new workbook that
only contains the
visible sheets, then another with only the Dock sheets
etc etc.
The number of sheets used each time varies widely. But if
the visible sheet
is not used, then neither are the other 3 in the set

Confused...try being in my seat =)

Regards
Peter

.