Sub
SheetsVisibleA()
Dim
wksWorksheet
As
Worksheet
For
Each
wksWorksheet
In
ActiveWorkbook.Worksheets
Select
Case
wksWorksheet.Visible
Case
xlSheetHidden
Debug.Print wksWorksheet.Name; wksWorksheet.Visible;
"xlSheetHidden"
Case
xlSheetVeryHidden
Debug.Print wksWorksheet.Name; wksWorksheet.Visible;
"xlSheetVeryHidden"
Case
Else
Debug.Print wksWorksheet.Name; wksWorksheet.Visible
End
Select
Next
wksWorksheet
Dim
wsNew
As
Worksheet
Set
wsNew = Worksheets.Add
With
wsNew
.Name =
"Data"
.Move after:=Sheets(Sheets.Count)
End
With
Set
wsNew =
Nothing
Dim
ws
As
Worksheet
Dim
X
As
Integer
X = 1
With
ActiveWorkbook.Sheets(
"Data"
)
For
Each
ws
In
ActiveWorkbook.Worksheets
If
ws.Name <>
"Data"
Then
.Cells(1, X) =
"Sheetname"
.Cells(1, X + 1) = ws.Name
ws.Range(
"A2:A3"
).Copy
.Cells(3, X).PasteSpecial xlPasteValues
ws.Range(
"E5,O5"
).Copy
.Cells(6, X).PasteSpecial xlPasteValues
ws.Range(
"E6:E77"
).Copy
.Cells(7, X).PasteSpecial xlPasteValues
ws.Range(
"O6:O77"
).Copy
.Cells(7, X + 1).PasteSpecial xlPasteValues
X = X + 3
End
If
Next
ws
End
With
Worksheets(
"Data"
).Range(
"4:4"
).Font.Bold =
True
Worksheets(
"Data"
).Range(
"6:6"
).Font.Bold =
True
End
Sub