VBA - Source data from multiple sheets

Question

I am attempting to create a new chart using source data from multiple sheets. The data will always be in the same column no matter the sheet. However, I would like to select the data from Range("F2", Range("F2").End(xlDown)) from each of the sheets following the permanent two sheets at the beginning.

The sheets that I want data extracted from will be used as source data for a chart that will be created and deleted as new sheets are added, and the cycle will be repeated.

So, for example, I would like it to grab data from sheets 3 to 52, or however many there are, from the range specified above so it becomes one cohesive source. I am not sure if there is a nice way to do this without using a hidden sheet. Thanks in advance.

Private Sub Locations()
Set Build = Charts.Add(After = Worksheets("Report"))
    With Build
        .SetSourceData
        .ChartType = xlColumnClustered
    End With
End Sub

@BruceWayne - I apologize, it is extremely unfinished, I was simply providing what I have so far. — A Cohen
– A Cohen, Commented Nov 29, 2017 at 15:23
Presumably you have more than one column if you are intending on plotting on 2 axes? Or will one be left to default to 1,2,3 etc? — QHarr
– QHarr, Commented Nov 29, 2017 at 15:26
@QHarr - Yes, The X-Axis will be the names of the buildings and the Y-Axis will be the number of times the buildings name was used in a cell within the range — A Cohen
– A Cohen, Commented Nov 29, 2017 at 15:28
If you are certain of sheet order you can loop from 3 to sheets.count (only 2 preceeding worksheets) calling a sub that extracts the required range and pastes to next available row in "master" consolidation range. Then another sub to create the chart. Otherwise loop checking sheet names not like A or B (the two you want to exclude). — QHarr
– QHarr, Commented Nov 29, 2017 at 15:34

Xabier · Accepted Answer · 2017-11-29 16:26:23Z

0

I have the code that would loop through Sheet3 onwards and add all the data from column B (excluding the header) to an array, then you can use the array to plot the values into your chart?

Sub foo()
Dim wb As Workbook
Dim ws As Worksheet
Dim Myarray() As Variant
y = 0

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet1" Then
        If ws.Name <> "Sheet2" Then
             LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
             Counter = Counter + LastRow
             ReDim Preserve Myarray(Counter)
             For Each cell In ws.Range("B2:B" & LastRow)
                 Myarray(y) = cell.Value
                 y = y + 1
             Next cell
        End If
    End If
Next ws

If you then want to write the contents of the array into a helper sheet:

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
For p = 1 To UBound(Myarray)
    Sheets("Temp").Cells(p, 1).Value = Myarray(p - 1)
Next p

Then find the last row and get the range into SetSourceData to update the chart.

edited Nov 29, 2017 at 16:26

answered Nov 29, 2017 at 15:56

Xabier

7,7251 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fernando J. Rivera Over a year ago

How would you plot values stored in an array?

Xabier Over a year ago

Personally I would probably place the values from the array into another Sheet and then use that range to plot the chart, alternatively there are some ways to plot straight from an Array, none of which I have ever used, but this might be of use for you: [link]daniweb.com/programming/software-development/threads/388319/…

Fernando J. Rivera Over a year ago

I've just found this Q, might be useful - my first thought was also to transfer the array into a helper sheet, but it seems it can be done directly: stackoverflow.com/questions/10570023/…

jeffreyweir · Accepted Answer · 2017-11-29 21:37:36Z

0

If you've got Excel 2016 or later, then the new PowerQuery functionality under the 'Get & Transform' section of the Data tab is perfect for this. (In previous versions, PowerQuery is available as a free addin from Microsoft that you'll have to install).

See the following link for an example of how it can be used to mash up data from multiple worksheets: excel indirect function to read dates and return dynamic values From there, it's a simple task to make a PivotTable and then PivotChart out of it, and it can automatically bring through new worksheets without the user having to make any changes.

answered Nov 29, 2017 at 21:37

jeffreyweir

4,8841 gold badge19 silver badges27 bronze badges

Comments

A Cohen · Accepted Answer · 2017-11-30 16:03:27Z

I was able to simplify the situation by using a hidden sheet

Private Sub Locations()

Dim ws As Worksheet, rep As Worksheet, LastRow As Double
With ThisWorkbook
    For n = 1 To Sheets.Count
        Set ws = Worksheets(n)
        Set rep = Worksheets("Report")
        LastRow = rep.Range("C1", rep.Range("C1").End(xlDown)).Rows.Count
        If IsNumeric(ws.Name) Then
            If rep.Range("C1") = "" Then
                ws.Range("F2", ws.Range("F2").End(xlDown)).Copy _
                Destination:=rep.Range("C1")
            Else:
                ws.Range("F2", ws.Range("F2").End(xlDown)).Copy _
                Destination:=rep.Range("C" & LastRow)
            End If
        End If
    Next n
End With

End Sub

Collectives™ on Stack Overflow

VBA - Source data from multiple sheets

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related