Can I copy just the visible sheets?

[Guest]

I realise I can make the sheets I want to copy visible, then copy them as per
below (using variables for sheet names)

Sheets(Array(CCRFsheet2, CCRFsheet3, CCRFsheet4a, CCRFsheet6,
CCRFsheet7a, CCRFsheet7b, CCRFsheet8, CCRFsheet9)).Copy

But is it possible to write a macro that will work out what sheets are
visible and then only copy those sheets to another WorkBook?

 

[Gord Dibben]

Trefor

No error trapping and Book4.xls must be open.


Private Sub Visble_Only()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then
ws.Copy Before:=Workbooks("Book4.xls").Sheets(1)
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Gord,

Thankyou for your reply. Not quite what I was after, but close enough. This
creates my sheets in reverse order in the new workbook and leaves the default
Sheet1, Sheet2, etc.

I modified and expanded your reponse, I am not sure if there is a better way
of doing this, but this works for me:

Dim ws As Worksheet, ws1 As Worksheet, x As Integer
Workbooks.Add
ActiveWorkBook.name
x = 0
For Each ws In ThisWorkbook.Sheets
If ws.Visible = True And InStr(ws.name, "Introduction") = 0 And
InStr(ws.name, "New Site Request") = 0 Then
x = x + 1
If x = 1 Then
ws.Copy After:=ActiveWorkBook.Sheets(1)
Set ws1 = ws
Else
ws.Copy After:=ActiveWorkBook.Sheets(ws1.name)
Set ws1 = ws
End If
End If
Next

For Each ws In ActiveWorkBook.Sheets
If Left(ws.name, 5) = "Sheet" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next

ActiveWorkBook.SaveAs
FileName:=ThisWorkbook.Sheets(CCRFsheet2).Cells(100, "A").Value, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False