Looking for a more condensed method of assigning values by using arrays and fixed ranges

Question

I currently have a Template Workbook set up that has charts and graphs updating when new data is plugged into the Data Tables.
The Data Tables are fixed ranges, and won't change.

The Macro is within the Template Workbook, which is why the below code doesn't set variables for each of the worksheets within the Template Workbook, and Code Names are used for each Worksheet name.

I know the below code is a very basic array code, and works. However, Is there a more condensed way to perform this array macro? I have researched, but can't seem to find an example of multiple ranges and assigning values from the Source Workbook - Multiple Worksheets to the Template Workbook - Multiple Worksheets.

Sub Main()
'Turn off screen updates and automatic calculations
 Application.ScreenUpdating = False
 Application.Calculation = xlManual
 Application.DisplayAlerts = False
 Dim wbDataSource As Workbook
 Dim ws As Worksheet
 Set wbDataSource = Workbooks("Segment Trends Data.xlsx")
 
 For Each ws In wbDataSource.Worksheets
 With ws
 If .Index <> 1 Then
 Dim Data(13) As Variant
 Data(0) = Worksheets("Report1").Range("A1").Value2
 Data(1) = Worksheets("Report1").Range("A5:H11").Value2
 Data(2) = Worksheets("Report2").Range("A1").Value2
 Data(3) = Worksheets("Report2").Range("A5:H11").Value2
 Data(4) = Worksheets("Report3").Range("A1").Value2
 Data(5) = Worksheets("Report3").Range("A5:H11").Value2
 Data(6) = Worksheets("Report4").Range("A1").Value2
 Data(7) = Worksheets("Report4").Range("A5:H11").Value2
 Data(8) = Worksheets("Report5").Range("A1").Value2
 Data(9) = Worksheets("Report5").Range("A5:H11").Value2
 Data(10) = Worksheets("Report6").Range("A1").Value2
 Data(11) = Worksheets("Report6").Range("A5:H11").Value2
 Data(12) = Worksheets("Report7").Range("A1").Value2
 Data(13) = Worksheets("Report7").Range("A5:H11").Value2
 End If
 End With
 Next ws
 
 wbDataSource.Close SaveChanges:=False
 With wsTTLUSCYTD
 .Range("A1").Value2 = Data(0)
 .Range("A5:H11").Value2 = Data(1)
 End With
 
 With wsCintiCYTD
 .Range("A1").Value2 = Data(2)
 .Range("A5:H11").Value2 = Data(3)
 End With
 
 With wsCOLCYTD
 .Range("A1").Value2 = Data(4)
 .Range("A5:H11").Value2 = Data(5)
 End With
 
 With wsDaytonCYTD
 .Range("A1").Value2 = Data(6)
 .Range("A5:H11").Value2 = Data(7)
 End With
 
 With wsIndyCYTD
 .Range("A1").Value2 = Data(8)
 .Range("A5:H11").Value2 = Data(9)
 End With
 
 With wsLouisCYTD
 .Range("A1").Value2 = Data(10)
 .Range("A5:H11").Value2 = Data(11)
 End With
 
 With wsCoreMktCYTD
 .Range("A1").Value2 = Data(12)
 .Range("A5:H11").Value2 = Data(13)
 End With
 
 Calculate
 
 Dim TemplatePath As String
 TemplatePath = "C:\Users\cday\OneDrive - udfinc.com\Budgeting Presentation_Working Files\"
 ActiveWorkbook.SaveAs Filename:=TemplatePath & "Segment Trends - CYTD - Budget Template" & ".xlsm", FileFormat:=52
'Turn screen updates and automatic calculations back on
 Application.ScreenUpdating = True
 Application.Calculation = xlAutomatic
 Application.DisplayAlerts = True
End Sub

Using Debug.Print, the data is pulling from the wbDataSource worksheets within the Loop. Debug.Print

• PeterT: Does this code work for you? There are a few problems that jump out to me right away: you are opening up wbDataSource, but when you're pulling data from the ReportX worksheets, you're not getting data from the wbDataSource workbook. The data is actually coming from whatever your ActiveWorkbook is currently (and it's not wbDataSource!). Also, you are looping through all the worksheets in a workbook, but it's not necessary because you are explicitly specifying which worksheet's data is copied to a Data array slot. Assigning the other sheet objects (wsCintiCYTD) is not shown.
• CDay: @PeterT Yes, this works for me. I'm not sure why you're saying the data is not coming from wbDataSource, because I am using For each ws in wbDataSurce.worksheets, With ws` to the the .value2 of each range. I specified the worksheet by it's name: Worksheets("Report1").range Worksheets("Report2").range, etc..
• CDay: @PeterT I didn't need to assign the other sheet objects, that are receiving the data, because the module is within that Workbook with Code Names
• PeterT: In your For Each ws In wbDataSource.Worksheets loop, your only reference to anything in wbDataSource is when you are checking .Index because that is explicitly using the With ws clause. So your reference is really If ws.Index <> 1 Then. Inside that loop, there are no other references to ws. Also, all of your .Value2 references are accessing the currently active worksheet, NOT necessarily wbDataSource. Simply opening another workbook doesn't guarantee to make it active. And nothing in your With ws clause is used for any of the .Value2 references.
• PeterT: Additionally, let's say your wbDataSource workbook has 12 worksheets. Your loop to set the Data array accessing the exactly same data 12 times, completely regardless of how many worksheets there are, or what is on those worksheets. There's no reason to loop since you're explicitly getting 14 .Value2 values from very specific worksheets. So the loop is not necessary.

Answer 1

The comment discussion in the original post directly relate to the differences between the ActiveWorkbook and ThisWorkbook. While you code may indeed work, it's not a guarantee. This reference (and this one) have good explanations to illustrate the differences between how you reference a workbook and why you should always pay specific attention to references (whether it's to the Workbook or Worksheet or Range).

As explained in the comments, because you specifically identify the worksheet for all the .Value2 data, you don't need to loop through all the worksheets. This will reduce the setup of your array to

Dim wbDataSource As Workbook
Set wbDataSource = Workbooks("Segment Trends Data.xlsx")
Dim Data(13) As Variant
With wbDataSource
 Data(0) = .Worksheets("Report1").Range("A1").Value2
 Data(1) = .Worksheets("Report1").Range("A5:H11").Value2
 Data(2) = .Worksheets("Report2").Range("A1").Value2
 Data(3) = .Worksheets("Report2").Range("A5:H11").Value2
 Data(4) = .Worksheets("Report3").Range("A1").Value2
 Data(5) = .Worksheets("Report3").Range("A5:H11").Value2
 Data(6) = .Worksheets("Report4").Range("A1").Value2
 Data(7) = .Worksheets("Report4").Range("A5:H11").Value2
 Data(8) = .Worksheets("Report5").Range("A1").Value2
 Data(9) = .Worksheets("Report5").Range("A5:H11").Value2
 Data(10) = .Worksheets("Report6").Range("A1").Value2
 Data(11) = .Worksheets("Report6").Range("A5:H11").Value2
 Data(12) = .Worksheets("Report7").Range("A1").Value2
 Data(13) = .Worksheets("Report7").Range("A5:H11").Value2
End With

Notice that I've changed the With clause to reference the wbDataSource workbook so that's it's very clear where all your data is coming from.

The code you've written actually doesn't need an array, in fact the array obscures what the code is really trying to do. What exactly is Data(0) or Data(11)? You might find it tedious, but I would more clearly define what information all those variables hold. As an example

Dim reportTitle As String
Dim reportDescription As String
Dim regionNumber As Long
Dim regionManager As String
With wbDataSource
 reportTitle = .Worksheets("Report1").Range("A1").Value2
 reportDescription = .Worksheets("Report1").Range("A5:H11").Value2
 regionNumber = .Worksheets("Report2").Range("A1").Value2
 regionManager = .Worksheets("Report2").Range("A5:H11").Value2
 ...
End With

Now when you come back to make an update to your code in six months, you don't have to remember what each of those array slots actually means.

Finally, getting back to the original question of your post, there's no magic or easy way to make the assignments to multiple destination worksheets. You've already defined the code names for each of the sheets, so the VBA you have is about as simple as you can make it. I still recommend the change to more descriptive variable names to make it clear which data you are copying to the destination worksheets.

With wsTTLUSCYTD
 .Range("A1").Value2 = reportTitle
 .Range("A5:H11").Value2 = reportDescription
End With
With wsCintiCYTD
 .Range("A1").Value2 = regionNumber
 .Range("A5:H11").Value2 = regionManager
End With