Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific worksheets & copy - code problem
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 -- BeSmart |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select specific sheets and copy to new workbook | Excel Programming | |||
Select any row, copy data from specific columns | Excel Programming | |||
select specific cells and consolidate same over many worksheets | New Users to Excel | |||
find specific data in row and select and copy entirerow | Excel Programming |