1
\$\begingroup\$

This code is applied to a master workbook with tabs containing characteristic information e.g. "Reliability standards", "Targets" etc. about each utility. The utilities of interest are selected by the user in the "Dashboard" tab

The excel sheet has a tab "Dashboard" that allows any of a maximum of 23 utilities to be selected so that data can be imported associated with those utilities and calculations applied to them / charts plotted. The list of utilities to be selected is in column 7 of the "Dashboard" tab in rows 8 through 30, with the tick boxes in column 8 (If tick-box is selected, the value is "True").

Obviously a lot of this code is repeated when cycling through each utility when opening the relevant tabs e.g "Reliability standards", "Targets", "Data quality scores" etc. I want to get rid of some of the repeat code so that it runs quicker e.g. through using lists or for loops applied only to utilities that are selected. It currently is very slow and often crashes - I think if I could replace some of the repeat text with loops and avoid using "Sheets.Activate" it would be faster but I don't know enough VBA to do that in the time I have.


Sub UpdateSelection()
'

Dim x As Integer
Dim xmax As Integer

Application.ScreenUpdating = False

Sheets("Reliability standards").Activate  'Hiding all utility columns on each worksheet
Cells.EntireColumn.Hidden = False
Range("E:EL").Select  'Fewer errors when this action is split in two, allows comments in cells
Selection.EntireColumn.Hidden = True

Sheets("Targets").Activate
Cells.EntireColumn.Hidden = False
Range("F:PZ").Select
Selection.EntireColumn.Hidden = True

Sheets("Data quality scores").Activate
Cells.EntireColumn.Hidden = False
Range("D:PA").Select
Selection.EntireColumn.Hidden = True

Sheets("Utility scale RoS results").Activate
Cells.EntireColumn.Hidden = False
Range("B:JQ").Select
Selection.EntireColumn.Hidden = True

Sheets("Electricity Access Indicators").Activate
Cells.EntireColumn.Hidden = False
Range("E:HZ").Select
Selection.EntireColumn.Hidden = True

Sheets("Customer Service Standards").Activate
Cells.EntireColumn.Hidden = False
Range("E:NH").Select
Selection.EntireColumn.Hidden = True

Sheets("CSI Visualisation data").Activate
Cells.EntireColumn.Hidden = False
Range("E:AX,BC:CV").Select
Selection.EntireColumn.Hidden = True

Sheets("RoS Visualisation data").Activate
Cells.EntireColumn.Hidden = False
Range("E:AX,BC:CV").Select
Selection.EntireColumn.Hidden = True


xmax = 23 'Number of listed utilities

For x = 1 To xmax 'For each utility

If Worksheets("Dashboard").Cells(7 + x, 8) = True Then 'Checks if the utility checkboxes are selected

  Sheets("Reliability standards").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select 'Find selected utility
  Selection.EntireColumn.Hidden = False 'Unhide selected utility
  
  Sheets("Targets").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False
  
  Sheets("Data quality scores").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False
  
  Sheets("Utility scale RoS results").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False
  
  Sheets("Electricity Access Indicators").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False

  Sheets("Customer Service Standards").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False
  
  Sheets("CSI Visualisation data").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False
  Cells(5, 50 + Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False

  Sheets("RoS Visualisation data").Activate
  Cells(5, Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False
  Cells(5, 50 + Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7 + x, 7).Value, Range("5:5"), 0)).Select
  Selection.EntireColumn.Hidden = False
  
  End If

Next x

Application.GoTo Worksheets("Reliability standards").Range("A1"), Scroll:=True 'Bringing view to top left corner
Application.GoTo Worksheets("Targets").Range("A1"), Scroll:=True
Application.GoTo Worksheets("Data quality scores").Range("A1"), Scroll:=True
Application.GoTo Worksheets("Utility scale RoS results").Range("A1"), Scroll:=True
Application.GoTo Worksheets("Electricity Access Indicators").Range("A1"), Scroll:=True
Application.GoTo Worksheets("Customer Service Standards").Range("A1"), Scroll:=True
Application.GoTo Worksheets("CSI Visualisation data").Range("A1"), Scroll:=True
Application.GoTo Worksheets("RoS Visualisation data").Range("A1"), Scroll:=True

Sheets("Dashboard").Activate

Application.Calculation = xlAutomatic
Application.Calculation = xlManual

Application.ScreenUpdating = True


End Sub

\$\endgroup\$
1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.