VBA/Macro help - Compile error

[Tippelschniff]

Hi all,

I have a dashboard that was changing column format when i refresh the data connection so have had a crack at recording a macro. i have no prior experience using macros and simply recorded steps (purely to amend col. formatting on opening of file) to give me the following. However when run it produces a "Compile error: expected End sub". Can anyone see the issue or educate me if there is a blinding error made?

Private Sub workbook_open()
Sub Convert_dates()
'
' Convert_dates Macro
' Converts date format to ddmmyyyy
'

'
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    Range("N:N,P:P,Q:Q,T:T,V:V").Select
    Range("Table_owssvr[[#Headers],[First Visit Date]]").Activate
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    Range("N:N,P:P,Q:Q,T:T,V:V,Y:Y,AB:AB").Select
    Range("Table_owssvr[[#Headers],[Third Visit Date]]").Activate
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    Range("N:N,P:P,Q:Q,T:T,V:V,Y:Y,AB:AB,AH:AH,AL:AL").Select
    Range("Table_owssvr[[#Headers],[Date Systems Updated]]").Activate
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    Range("N:N,P:P,Q:Q,T:T,V:V,Y:Y,AB:AB,AH:AH,AL:AL,AM:AM,AO:AO").Select
    Range("Table_owssvr[[#Headers],[Modified]]").Activate
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    Selection.NumberFormat = "m/d/yyyy"
End Sub
End Sub

Much appreciated in advance!

 

[RoryA]

Welcome to the forum.

You can't put one sub inside another like that. You also have a lot of unnecessary lines there. Try this:

Private Sub workbook_open()
'
' Convert_dates Macro
' Converts date format to ddmmyyyy
'

'

    Range("N:N,P:P,Q:Q,T:T,V:V,Y:Y,AB:AB,AH:AH,AL:AL,AM:AM,AO:AO").NumberFormat = "m/d/yyyy"
End Sub

 

[Tippelschniff]

Hi Rory - thank you yes I suspected my scrolling had no impact, much appreciated!

i assume i am able to stack sub-queries (once I've had a play with VBA and have some comprehension of the logic/syntax)?

 

[RoryA]

You can call multiple other macros from within a macro, you just can't define them within each other (well, you can using GoSub, but you shouldn't).