VBA calling specific ranges

[Excelllllllllllllllllllll]

Hi all, I'll break this down into 2 parts - the problem and what I've come up with.

1. I have a line graph that I'd like to change the source data for depending on 2 cell change macro.
In sheet "By Day" I have 53 weeks and 7 days in them (columns B to NH or 2-372). And I have 94 sites (rows 5-99).

In a different sheet "Daily Graph" I have 1 cell with a dropdown menu for Mon-Sun (A2) and another cell where you can select all the site names (B2). And I have a line graph.

I need this line graph to show all the days selected for the site selected. E.g. if Site 3 and Tuesday are selected I need the graph to show me 53 points corresponding to the 53 Tuesdays that relate to site 3.

2. What I have right now.

I'm not very experienced in excel VBA and I tried doing something with integer by setting "For i = 2 to 372 Step 7" for columns, but I can't make it select them all together and I have no idea how to select the proper site.
Now I'm trying to manually make a macro with IFs that will cover ALL possible days and site combinations (which is going to be very time consuming), butt I'm stuck at how to add several source cells into the graph. Right now I have:

[COLOR=#000000][FONT=Arial]ActiveSheet.ChartObjects("<wbr>Chart 1").Activate[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]ActiveChart.SetSourceData Source:=Sheets("By Day").Cells(5, 2)
[/FONT][/COLOR]

But I actually need something like

ActiveSheet.ChartObjects("<wbr style="font-size: 13.3333px;">Chart 1").Activate[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]    ActiveChart.SetSourceData Source:=Sheets("By Day").Cells(5, 2) [/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]and Sheets("By Day").Cells(5, 9)​ and Sheets("By Day").Cells(5, 16)​[/FONT][/COLOR][COLOR=#000000][FONT=Arial]
[/FONT][/COLOR]

Still, this is only for the specific site that is in Row 5 and I'll have to do this 6 more times for that site and 93 more times times 7 for the other sites.

If anyone has a better idea of how I can achieve my goal, I would be forever indebted to your friendly and helpful self.
If not, I would be equally as grateful if someone helps me figure out how to stop getting an error using the code that I've come up with.

Either way, I thank you guys for reading this far and (maybe) helping me out!

 

[NdNoviceHlp]

Select an area of your wb with no data. Use this area as the source for making all charts (ie. code to chart from this source area). Use VBA to move the data your interested in to this source area and then chart it. HTH. Dave

 

[Excelllllllllllllllllllll]

Perfect! That definitely saves me the trouble of figuring out how to VBA code for graph changes. Thank you!

Do you know how I can do that without writing close to 700 lines of code for each individual combination of site + day?

 

[Excelllllllllllllllllllll]

I had another thought - I could write the formula as =IF(AND(site name + monday etc etc) but that still leaves me with writing a 700 line long formula even if it saves me from even having it is a macro enabled excel file to begin with ... and it makes the whole thing a lot more user-friendly with a smaller file size.
But since I don't want to write 700 IF structures, how do I go about using VLOOKUP in this situation where I need to skip several cells? Or would something else be more appropriate to use?

 

[NdNoviceHlp]

Likely VBA is the only way to go. U can trial this on a copy of your wb.... don't use it on the original. This code uses the Daily graph sheet to move your data to from "by day" sheet. Use a select case code to transform selections Monday=1; Tuesday=2;etc. The row number is the site number so to operate this code....

Call MakeChartData(2, 1)' (Monday(s), site 1)
Call MakeChart

Here's the code. It transfers data, makes a chart, makes a picture file of the chart, removes the chart and data, removes the previous picture of the chart, inserts a picture of the chart and sizes it in a range and then removes the picture file. HTH. Dave

 Option Explicit
Sub MakeChartData(DayInput As Integer, RowInput As Integer)
Dim I As Integer, Cnt As Integer
'Dayinput: Monday=2; Tuesday=3; Wednesday=4; etc.
'rowinput= site#
Cnt = 1
For I = DayInput To 372 Step 7
Sheets("Daily Graph").Cells(Cnt, 1) = Sheets("By Day").Cells(RowInput, I)
Cnt = Cnt + 1
Next I
End Sub

Sub MakeChart()
'make chart
'use named range to chart
Dim X1Value As Range, Y1Value As Range, ChartRange As Range
Dim FName As String, Sh As Shape, P As Object
Set X1Value = Sheets("Daily Graph").Cells(1, 1)
Set Y1Value = Sheets("Daily Graph").Cells(53, 1)
Set ChartRange = Sheets("Daily Graph").Range(X1Value, Y1Value)
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Daily Graph"
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
With ActiveChart
.HasLegend = False
.HasTitle = True
.ChartTitle.AutoScaleFont = False
.ChartTitle.Characters.Text = "Day of Week"
.ChartTitle.Characters.Font.Size = 12
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.AutoScaleFont = False
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Site#"
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 10
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Site Value"
End With
'y format
With Sheets("Daily Graph").ChartObjects(1).Chart.Axes(xlValue)
.HasMajorGridlines = True
.MajorGridlines.Border.ColorIndex = 17
.TickLabels.NumberFormat = "#0"
.TickLabels.AutoScaleFont = True
End With

'x format
With Sheets("Daily Graph").ChartObjects(1).Chart.Axes(xlCategory)
.HasMajorGridlines = True
.MajorGridlines.Border.ColorIndex = 17
.TickLabels.NumberFormat = "#0"
.TickLabels.AutoScaleFont = True
End With

'export chart return to normal
FName = ThisWorkbook.Path & "\" & "ChartSite.gif"
ActiveChart.Export Filename:=FName, FilterName:="GIF"
Sheets("Daily Graph").Range("A1:A53").ClearContents
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
ActiveChart.Parent.Delete

With Sheets("Daily Graph")
For Each Sh In .Shapes
If Application.Version > 12 Then
If Sh.Type = 11 Then
Sh.Delete
End If
Else
If Sh.Type = 13 Then
Sh.Delete
End If
End If
Next Sh
End With

With Sheets("Daily Graph").Range("D3:n20")
'import picture
Set P = .Parent.Pictures.Insert(ThisWorkbook.Path & "\" & "ChartSite.gif")
'position picture
P.ShapeRange.LockAspectRatio = msoFalse
P.Top = .Top
P.Left = .Left
P.Width = .Width
P.Height = .Height
End With
Set P = Nothing
Kill (ThisWorkbook.Path & "\" & "ChartSite.gif")
End Sub