Challenges: Monday Morning Mulling: August 2019 Challenge
2 September 2019
On the final Friday of each month, we set a Power BI or Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.
To recap, the problem we presented last week was to challenge you to create multiple worksheets from a data list. The intention is to dynamically create the worksheets without manual intervention.
Essentially, we want to generate multiple worksheets with the names from the data list shown below:
Suggested Solution
One simple way here is to use VBA to look through the value of each cell in the data list and assign the value to each worksheet created after the Summary worksheet.
The first step is to set up a named range as shown below. We define the cell range A2 to A13 as the data list and assign a name tag Source to this range as shown below.
The next step is to declare the relevant variables.
Dim wksht As Worksheet
Dim rng As Range
Dim sName As String
Then, we set the variable wksht as the active worksheet and turn off the ScreenUpdating application. If ScreenUpdating property were set to false, it will speed up the macro operation.
Set wksht =ActiveSheet
Application.ScreenUpdating = False
Next, we used an ‘For Loop’ to look up each value in the named range and assign the range value to the string variable sName. Then, we use an ‘If’ statement to determine the length of the variable sName. If the length is greater than zero, then we add a new worksheet after the last page and set the worksheet name equal to the value stored in variable sName, viz.
For Each rng In Range("Source")
sName = rng.Value
If Len(sName) > 0 Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sName
End If
Next rng
Next, we go back to the summary worksheet and turn on the ScreenUpdating application once more.
Worksheets("Summary").Select
Application.ScreenUpdating = True
Combing all the lines of code together, we get this:
Sub AddWorksheetsFromSelection()
Dim wksht As Worksheet
Dim rng As Range
Dim sName As String
Set wksht = ActiveSheet
Application.ScreenUpdating = False
For Each rng In Range("Source")
sName = rng.Value
If Len(sName) > 0 Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sName
End If
Next rng
Worksheets("Summary").Select
Application.ScreenUpdating = True
End Sub
By using this method, we could create multiple worksheets from the named range without repetitive manual intervention.
The Final Friday Fix will return on 27th September with a new Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.
