Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: Unable to get Macro to run on multiple worksheets in Excel

    Hi Guys,

    Very new to this i'm afraid and have very little expirence of VB so i'm hoping some1 here can save me!

    I have an Excel workbook which contains 10 worksheets. I want to be able to run a macro which will run on the first worksheet, then run on the 2nd, 3rd, etc until it has completed on each active worksheet within the workbook.

    I have amended the original macros (which is highlighted in RED) but am now getting a Compile Error: END if without block If.

    Sure its probably just a syntax error, and easy to sort out but i little VB experience.

    Many thanks for any help!!!

    Jim

    Sub ArrivalsReportingNew5()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets

    '
    ' ArrivalsReportingNew4 Macro
    ' Macro recorded 18/12/2008 by JACKSON
    '
    ' Keyboard Shortcut: Ctrl+Shift+T
    '
    Rows("1:1").Select
    Selection.Font.Bold = True
    Cells.Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Cells.EntireColumn.AutoFit
    Range("E:E,I:I,J:J").Select
    Range("J1").Activate
    ActiveWindow.LargeScroll ToRight:=0
    Columns("E:E").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.NumberFormat = "0.0%"
    Range("E:E,I:I,J:J").Select
    Range("J1").Activate
    ActiveWindow.LargeScroll ToRight:=3
    Range("E:E,I:I,J:J,AU:AU,AW:AW").Select
    Range("AW1").Activate
    ActiveWindow.LargeScroll ToRight:=2
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 48
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 1
    Selection.NumberFormat = "$ #,##0"
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 43
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("H:H").Select
    ActiveWindow.LargeScroll ToRight:=3
    Range("H:H,AP:AP,AQ:AQ,AS:AS").Select
    Range("AS1").Activate
    ActiveWindow.LargeScroll ToRight:=0
    Selection.NumberFormat = "0.0"
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.LargeScroll ToRight:=3
    Columns("AV:AV").Select
    Selection.NumberFormat = "0.0"
    ActiveWindow.LargeScroll ToRight:=-3
    Range("B2").Select
    ActiveWindow.FreezePanes = True
    End If
    Next ws

    End Sub

  2. #2
    Join Date
    Feb 2009
    Posts
    2
    Ok, i have since discovered my error, and it is (i think???) that I have omitted the IF statement. Is this the case? and if so where in my script does this sit?

    Please help!! :-)

    Many thanks

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •