VBA macro code to expand a certain column

[Ryanchris]

Hi all,

Looking for code that will allow me to expand a certain column range. I have a button on one worksheet (sheet1, Dashboard), that when pressed, needs to take me to an now expanded column on Sheet15 called Job Summary. The columns to expand on Sheet 15 are AR:BI. I thought the code below would work but it only works if I run it when I am actually on Sheet15. If I run it from the button on Sheet1, it stays on sheet1 and does not open the group on Sheet15.

Sub Expand_JobSummary()
Dim wks As Worksheet


Worksheets("sheet15").Outline.ShowLevels rowlevels:=1, columnlevels:=1
Columns("AR:BI").Select
Selection.EntireColumn.Hidden = False




End Sub

I am a beginner to VBA - what did I miss?

Thank you so much!

[Bob Phillips]

Is this what you want?


Sub Expand_JobSummary()

With Worksheets("Sheet15")

.Outline.ShowLevels rowlevels:=1, columnlevels:=1
.Columns("AR:BI").Hidden = False
End With
End Sub

[Ryanchris]

Works perfect! Going to head of and learn about with statements now...figure out why it works!

Thank you!

[Bob Phillips]

It is not the with statement per se that makes it work, it is making every expression apply to that worksheet. The code that I gave is the same as


Sub Expand_JobSummary()

Worksheets("Sheet15").Outline.ShowLevels rowlevels:=1, columnlevels:=1
Worksheets("Sheet15").Columns("AR:BI").Hidden = False
End Sub

By encapsulating the two statements within the With … End With it ensures that each applies to Sheet15. Adding the With … End With makes it more readable (IMO), and is more efficient as the object is only evaluated the once, not every time.