How to make comparison chart to display two varying sets of data.

[Matrix2021]

Hello.

I am not sure if you can help me. I just don't know...

I would love to know, how to make comparison chart to display two varying sets of data.

I have two workbooks.

One of the workbook have 181 worksheets. "Each worksheet name after a country and have its own data.

Next

The other workbook have 185 worksheets."Each worksheet name after a country and have its own data.

This is the hard part:

#1 How can I link both workbook data together using macros? Country vs Country or USA vs USA as an example.

#2 How can I create a comparison chart to display two varying sets of data, both varying sets of data beginning with the zero values?

If you think I sound crazy. Please just say so lol. I am not sure if you are open in helping me. If so, I am looking for someone to point me to the right direction.

Thanks in advance

Cheers,


https://drive.google.com/file/d/1mmsL54lWBSVNsrP0INleJe-uW8U-2VVD/view?usp=sharing


https://drive.google.com/file/d/1JigNJKkY2IMGg4-aSXu0NnFnRRuvb6jx/view?usp=sharing

[SioSio]

In my environment I can't see the link you provided, so it may be an irrelevant answer.

First, prepare two excel sheets(Ex. sheet1 and sheet2).
prepare two combobox and two commandbutton in the userform.
The two combobox store the sheet names of the two books In UserForm_Initialize() function.
Select an item from each of the two combobox.
Click the first button to link Sheet1 and Sheet2 with the sheets of the two books selected in the combobox in CommandButton1_Click() function.
Click the second button to display a scatter plot of the data on the two sheets in CommandButton2_Click() function..
Modify data range used to draw the graph, graph type and each Const data for your envronment.

Dim SheetA As String
Dim SheetB As String
Const Path As String = "C:\Excel_data\"
Const BookA_fname = "BookA.xlsx"
Const BookB_fname = "BookB.xlsx"
Const BookA As String = Path & BookA_fname
Const BookB As String = Path & BookB_fname
 
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Workbooks.Open Filename:=BookA
    With ComboBox1
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks.Open Filename:=BookB
    With ComboBox2
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub
 
Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub
 
Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub
 
Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).value = "='" & Path & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).value = "='" & Path & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub
 
Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub

[Matrix2021]

Hello SioSio.

I am not sure if I can fix this. I am trying my best to follow your teaching. I am not sure what I have done is correct and it seems like I am not getting the results.

Please SioSio. If you are willing please let me know as I have place the codes in the files below.

Let me know, how to move forward

This is a short video that explain my problem: https://drive.google.com/file/d/1u6d...ew?usp=sharing

Thanks in advance

https://drive.google.com/file/d/1qLW...ew?usp=sharing

https://drive.google.com/file/d/1mtE...ew?usp=sharing

[SioSio]

It seems that the contents I wrote before did not reach you well, so I will show you the procedure again.

1. Create a "new" excel book.

2.Prepare two excel sheets(Ex.sheet1 and sheet2).

3.developter tab
Create a Developer tab for your convenience. To turn on the Developter tab, execute the following steps.
3.1. Right click anywhere on the ribbon, and then click Customize the Ribbon.
3.2.Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).
3.3.Check the Developer check box.
3.4.Click OK.
3.5. You can find the Developer tab next to the View tab.

4. Click "Visual Basic" on the Developer tab to open the vba editor.

5.Click Insert, "User form".If the Toolbox does not appear automatically, click View, Toolbox.

After this, create two comboboxes and two buttons according to the contents written earlier.

P.S.
The link you posted is not visible in my environment.
Please use the tools in "Post your reply".

[Matrix2021]

SioSio

Thanks for your feedback. I greatly appreciate it.
I have followed your second comments, but when it comes to the coding. I am not sure if I made a mistake. I have receive an error which states: "Compile error: Invalid qualifier".

Please view code and image for a better understanding.

Thanks in advance for your swift reply.

Private Sub UserForm_Click()
 
End Sub
Dim SheetA As String
Dim SheetB As String
Const Path As String = "C:\Excel_data\"
Const BookA_fname = "C:\Users\Corey\Desktop\Unemployment_Rate"
Const BookB_fname = "C:\Users\Corey\Desktop\GDP_Annual_Growth_Rate_%"
Const BookA As String = Path & BookA_fname
Const BookB As String = Path & BookB_fname
 
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Workbooks.Open Filename:=Unemployment_Rate.xlsx
    With ComboBox1
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks.Open Filename:=GDP_Annual_Growth_Rate_%.xlsx
    With ComboBox2
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub
 
Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub
 
Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub
 
Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).Value = "='" & Path & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).Value = "='" & Path & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub
 
Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub

[SioSio]

1. Delete this line.

Private Sub UserForm_Click()
 
End Sub

2. Specify the following character string for the constant.

'Specify the path of Book A.
'(Ex.)
'Const Path1 As String = "C:\Excel_data1\"
Const Path1 As String = "Your excel Book A path\"
 
'Specify the path of Book B.
'(Ex.)
'Const Path2 As String = "C:\Excel_data2\"
Const Path2 As String = "Your excel Book B path\"
 
'Specify the file name of Book A
'(Ex.)
'Const BookA_fname As String = "Unemployment_Rate.xlsx"
Const BookA_fname As String = "Unemployment_Rate.xlsx"
 
'Specify the file name of Book B
'(Ex.)
'Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
 
'Specify the full path of Book A. No need to change.
Const BookA As String = Path1 & BookA_fname
 
'Specify the full path of Book B. No need to change.
Const BookB As String = Path2 & BookB_fname

3. In the Sub UserForm_Initialize() subroutine, restore the following.(No need to change.)

    Workbooks.Open Filename:=Unemployment_Rate.xlsx
        '｜
         'V
    Workbooks.Open Filename:=BookA
    Workbooks.Open Filename:=GDP_Annual_Growth_Rate_%.xlsx
        '｜
         'V
    Workbooks.Open Filename:=BookB

4.By changing the constant, change the variable "path" of Private Sub CommandButton2_Click () to "path1" and "path2".

Private Sub CommandButton2_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
 
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).value = "='" & Path1 & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
 
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).value = "='" & Path2 & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
 
End Sub

In the example I showed, It put a link of A1:B5 of BookA to Sheet1 and a link of A1:B5 of BookB to Sheet2, and draw a scatter plot of them.
Change the variables i1 and i2, j1 and j2 of Private Sub CommandButton1_Click () and change the ange ("A1: B5") of Private Sub CommandButton2_Click () according to your environment.

[Matrix2021]

SioSio. I must say to you again... Thank you for your patience and support.

I have followed your instructions and tuition. Please identify below if the code have been plugin into the right location and please view the screen shot of the error and other photos that supports what I am trying to accomplish.

Thanks in advance SioSio.

1.jpg
2.jpg
3.jpg
4.jpg
5.jpg

Dim SheetA As String
Dim SheetB As String
'Specify the path of Book A.
'(Ex.)
'Const Path1 As String = "C:\Excel_data1\"
Const Path1 As String = "Your excel Book A path\"
 
'Specify the path of Book B.
'(Ex.)
'Const Path2 As String = "C:\Excel_data2\"
Const Path2 As String = "Your excel Book B path\"
 
'Specify the file name of Book A
'(Ex.)
'Const BookA_fname As String = "Unemployment_Rate.xlsx"
Const BookA_fname As String = "Unemployment_Rate.xlsx"
 
'Specify the file name of Book B
'(Ex.)
'Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
 
'Specify the full path of Book A. No need to change.
Const BookA As String = Path1 & BookA_fname
 
'Specify the full path of Book B. No need to change.
Const BookB As String = Path2 & BookB_fname
 
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
        Workbooks.Open Filename:=Unemployment_Rate.xlsx
        '|
         'V
    Workbooks.Open Filename:=BookA
    Workbooks.Open Filename:=GDP_Annual_Growth_Rate_%.xlsx
        '|
         'V
    Workbooks.Open Filename:=BookB
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub
 
Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub
 
Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub
 
Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).Value = "='" & Path & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).Value = "='" & Path & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub
 
Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub
 
 
 

[SioSio]

The base code is shown in #2.
#6 shows only the code in #2 and the modified portion of your code shown in #5.

Dim SheetA As String
Dim SheetB As String
Const Path1 As String = "C:\Users\Corey\Desktop\"
Const Path2 As String = "C:\Users\Corey\Desktop\"
Const BookA_fname As String = "Unemployment_Rate.xlsx"
Const BookB_fname As String = "GDP_Annual_Growth_Rate_%.xlsx"
Const BookA As String = Path1 & BookA_fname
Const BookB As String = Path2 & BookB_fname
 
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Workbooks.Open Filename:=BookA
    With ComboBox1
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks.Open Filename:=BookB
    With ComboBox2
        For Each ws In ActiveWorkbook.Worksheets
            .AddItem ws.Name
        Next
    End With
    Workbooks(BookA_fname).Close
    Workbooks(BookB_fname).Close
End Sub
 
Private Sub ComboBox1_Change()
    SheetA = ComboBox1.List(ComboBox1.ListIndex)
End Sub
 
Private Sub ComboBox2_Change()
    SheetB = ComboBox2.List(ComboBox2.ListIndex)
End Sub
 
Private Sub CommandButton1_Click()
'Link both workbook data
    Dim i1 As Long
    Dim j1 As Long
    Dim i2 As Long
    Dim j2 As Long
    Worksheets("Sheet1").Select
    For i1 = 1 To 5
        For j1 = 1 To 2
            Cells(i1, j1).value = "='" & Path1 & "[" & BookA_fname & "]" & SheetA & "'!R" & i1 & "C" & j1
        Next j1
    Next i1
    Worksheets("Sheet2").Select
    For i2 = 1 To 5
        For j2 = 1 To 2
            Cells(i2, j2).value = "='" & Path2 & "[" & BookB_fname & "]" & SheetB & "'!R" & i2 & "C" & j2
        Next j2
    Next i2
End Sub
 
Private Sub CommandButton2_Click()
'Plot Graph
    Range("A1:B5").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$5")
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$5"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub

[Matrix2021]

SioSio.

Hello,

The VBA coding is great. You have given me an other way to get similar results and I am thankful for the opportunity.

Will chat soon.

Thanks for taking the time in helping me move to the next level.

Greatly appreciate it.

Solve