Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro on every worksheet
I've had some helpful answers already but nothing I've tried has fully
succeeded. I can't seem to get the macro to advance to the next worksheet. I want to put a loop around this macro so that it will be run on every worksheet in the workbook. It is in a workbook named DATA COLLECTION and copies sheets to another workbook named DATA STORAGE AND RETRIEVAL. TIA Sub Data_Mover() Application.Run "'DATA COLLECTION.xls'!StopTimer_Collect" Windows("DATA COLLECTION").Activate Dim wksName As String wksName = ActiveSheet.Name Dim wbk As Workbook On Error Resume Next Set wbk = Workbooks("DATA STORAGE AND RETRIEVAL.xls") On Error GoTo 0 If wbk Is Nothing Then 'MsgBox "Opening DATA STORAGE AND RETRIEVAL" Set wbk = Workbooks.Open("P:\Bowling Green\QA DATA\QA DATA COLLECTION\DATA STORAGE AND RETRIEVAL.xls") Windows("DATA COLLECTION").Activate End If Application.DisplayAlerts = False 'not "are you sure prompt" On Error Resume Next 'in case it isn't there Workbooks("DATA STORAGE AND RETRIEVAL").Worksheets(wksName).Delete On Error GoTo 0 Application.DisplayAlerts = True Sheets(wksName).Select ActiveSheet.Unprotect Worksheets(wksName).Copy After:=Workbooks( _ "DATA STORAGE AND RETRIEVAL").Worksheets("DATA STORAGE AND RETRIEVAL") ActiveWindow.FreezePanes = False Rows("11:11").Select Selection.Insert Shift:=xlDown Rows("10:10").Select Selection.Copy Rows("11:11").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Rows("10:10").Delete Rows("1:7").Delete ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlNoSelection ActiveWindow.SelectedSheets.Visible = False Windows("DATA COLLECTION").Activate ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ActiveSheet.EnableSelection = xlUnlockedCells Application.Run "'DATA COLLECTION.xls'!StartTimer_Collect" End Sub -- n00b lookn for a handout :) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to name worksheet tabs using a cell within the worksheet? | Excel Discussion (Misc queries) | |||
error with macro to name new worksheet with cell from old worksheet | Excel Programming | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming | |||
macro to apply worksheet event to active worksheet | Excel Programming | |||
Record Worksheet Content as Macro and Execute from another Worksheet | Excel Programming |