Set variable in procedure from macro

[Noz2k]

I have a bit of code which looks through multiple sheets of data (each sheet represents 1 week) and then calculates the total across the sheets.

What I am now adding to this is the ability to chart the the values over a time period, on the click of a button. 1 button for each row in the table.

I can get this to work for each line in the table, but was wondering if there would be a quicker way other than copy and pasting the whole code, when the only change needed between rows is the value of Y.

Here is the code (this calculates for row 6, which means Y = 6)

Sub Chart1()
Dim ws1         As Worksheet, _
    ws2         As Worksheet, _
    wsm         As Worksheet, _
    p           As String, _
    NumLoops    As Long, _
    WeekSNum    As Long, _
    X           As Long, _
    DT          As Date, _
    DF          As Date, _
    Y           As Long, _
    YearStart   As Date
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set wsm = Sheets("Master")
ws1.Range("C5") = CDate(Sheets("Sheet1").txtDateFrom.Value)
ws1.Range("G5") = CDate(Sheets("Sheet1").txtDateTo.Value)
DF = (ws1.Range("C5") + 7) - (Weekday((ws1.Range("C5") + 7), 2))
DT = (ws1.Range("G5") + 7) - (Weekday((ws1.Range("G5") + 7), 2))
YearStart = ws1.Range("K30")


With wsm
   .Cells.Clear
End With
NumLoops = (DT - DF) / 7 + 1
''Sets the value for NumLoops based on 2 date values in sheet1
    
    WeekSNum = (DF - YearStart) / 7 + 1
''Sets the value for WeekSNum based on 2 values in sheet1

Y = 6    
X = 0
''Sets the start value for X and Y
Do Until X = NumLoops
p = "Week" & WeekSNum + X
'MsgBox p
If Sheets(p).Range("W3") = "Total" Then
    Sheets(p).Range("C" & Y, "U" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("W" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("Y3") = "Total" Then
    Sheets(p).Range("C" & Y, "X" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("Y" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AA3") = "Total" Then
    Sheets(p).Range("C" & Y, "Z" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AA" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AC3") = "Total" Then
    Sheets(p).Range("C" & Y, "AB" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AC" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AE3") = "Total" Then
    Sheets(p).Range("C" & Y, "AD" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AE" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AG3") = "Total" Then
    Sheets(p).Range("C" & Y, "AF" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AG" & Y).Copy wsm.Range("AE1").Offset(X, 0)
End If
X = X + 1
Loop
Sheets("Master").Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    r = 2
    C = 2
    With ActiveChart
    .SetSourceData Source:=wsm.Range("C1:C" & X)
    .SeriesCollection(1).Name = ws2.Range("C3")
Do Until r = 18
    .SeriesCollection.NewSeries
    .SeriesCollection(C).Values = wsm.Range("C1:C" & X).Offset(0, r)
    .SeriesCollection(C).Name = ws2.Range("C3").Offset(0, r)
    r = r + 2
    C = C + 1
    Loop
End With

 

[Sektor]

You can create array, fill it with values and assign it to Values or XValues properties of the chart.

 

[Norie]

Pass the value for Y to the sub instead of setting the value in the code.

You can do that by changing the header to this,

Sub Chart1(Y As Long)

and removing the declaration for Y and the line of code where you give it a value.

Now call it like this.

Chart1 6

or perhaps this which might make it clearer what you are doing.

Call Chart1(6)

 

[Noz2k]

Ah ok, that makes sense, and is a nicer way of doing it than setting a cell value and then assigning Y to that cell, and clearing at the end of the code.

Thanks for the help

 

[Noz2k]

Just a quick further question.

How do I set the starting value for the x-axis label?

At the moment it just goes 1, 2, 3, 4 etc

what line would I need to add to make it go 28, 29, 30, 31?

am assuming it's something starting with

.Axes(xlCategory)

but don't know how to reference the values under the tick marks, (and don't know how to do it not in vba either so I can't use the macro recorder)

 

[Noz2k]

I think it's the .minimumscale property I want to change, but it just gives me an error.

Can you not set this for line Graphs?

 

[Norie]

Well I was going to suggest recording a macro, that's how I would have done it.

It still might be worth a shot.

You might have to go through a few options and you'll undoubtedly end of with loads of code but if you can do it manually it should be recorded.

One thing you could do to cut down the effort would be to find out how you would do it manually.

You should be able to find that if you search, there are a few sites out there that are all about Excel charts.