Select specific worksheets & copy - code problem

[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

 

[Paul Robinson]

Hi
If you want to copy a sheet with "Planned" in A1 do this

If sh.Range("A1").Value = "Planned" then
sh.Copy
end if

Your code is a bit higgledy-piggledy after that. You don't seem to
paste the sheet anywhere within the same loop and then you seem to
start selecting other things in another loop.
Sorry to not be more help!
regards
Paul

 

[BeSmart]

Thanks Paul - that solved part of my problem...
i.e. worksheets that have "planned" entered into A1 were copied....
but they copied into separate workbooks??
How do I get them to all copy into one new workbook?

FYI
The second part of the code "clears out" functionality that is not required
in the new workbook
e.g.
- Formulas that lookup named ranges in the original workbook
(i.e. copy / paste values into same cells to clear the formula)

- Macro buttons where the macro lives in the original workbook
(i.e. delete specific buttons)

- Named ranges that live in the original workbook & cause "link" errors
(i.e. delete named ranges that are not "local" to this worksheet)