B
BeSmart
Hi All
I've tried to write the following code to:
- find worksheets in active workbook with "Planned" in cell A1,
- then select all those worksheets and copy them into one new workbook
- then format each sheet within the new workbook (e.g. select a named range
& copy/paste values etc)
The workbook and worksheet names are dynamic
I'm sure I've got the "End If"'s / "Next" in the wrong place (this always
confuses me)
At the moment it copies the active worksheet (which does not have "Planned"
in cell A1) and a blank new worksheet? - and then it stops.
Any help would be greatly appreciated
Sub Selectplanned()
Dim sh As Worksheet
Dim Rng As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Overview Template" And sh.Name <> "GRP Wkly Collection"_
And sh.Name <> "GRP Qtrly Collection" And sh.Visible = True Then
On Error Resume Next
Set Rng = sh.Range("A1") = "Planned"
On Error GoTo 0
If Rng Is Nothing Then
Else
Sheets.Select
End If
End If
Next sh
ActiveWindow.SelectedSheets.Copy
For Each sh In ActiveWorkbook.Worksheets
Application.Goto Reference:="Plannedrange"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="GRPpost"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'etc. etc.
Next sh
End Sub
I've tried to write the following code to:
- find worksheets in active workbook with "Planned" in cell A1,
- then select all those worksheets and copy them into one new workbook
- then format each sheet within the new workbook (e.g. select a named range
& copy/paste values etc)
The workbook and worksheet names are dynamic
I'm sure I've got the "End If"'s / "Next" in the wrong place (this always
confuses me)
At the moment it copies the active worksheet (which does not have "Planned"
in cell A1) and a blank new worksheet? - and then it stops.
Any help would be greatly appreciated
Sub Selectplanned()
Dim sh As Worksheet
Dim Rng As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Overview Template" And sh.Name <> "GRP Wkly Collection"_
And sh.Name <> "GRP Qtrly Collection" And sh.Visible = True Then
On Error Resume Next
Set Rng = sh.Range("A1") = "Planned"
On Error GoTo 0
If Rng Is Nothing Then
Else
Sheets.Select
End If
End If
Next sh
ActiveWindow.SelectedSheets.Copy
For Each sh In ActiveWorkbook.Worksheets
Application.Goto Reference:="Plannedrange"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="GRPpost"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'etc. etc.
Next sh
End Sub