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.

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