Hey,

I have a spreadsheet containing charts that are linked to cell range
within the worksheet. (last four weeks of data).

Every week, we must go in and move the source forward one colum
manually.

Is there any way of doing this in VBA. I tried recording a macro an
moving it, but it assigns the new range to a literal location of cells
so it isn't repeatable.

Please help if you can.

Thanks,
Erik J. Lon

--
ErikLon
-----------------------------------------------------------------------
ErikLong's Profile: http://www.excelforum.com/member.php...fo&userid=3262
View this thread: http://www.excelforum.com/showthread.php?threadid=52431

show the recorded code.

--
Regards,
Tom Ogilvy


"ErikLong" wrote:


Hey,

I have a spreadsheet containing charts that are linked to cell ranges
within the worksheet. (last four weeks of data).

Every week, we must go in and move the source forward one column
manually.

Is there any way of doing this in VBA. I tried recording a macro and
moving it, but it assigns the new range to a literal location of cells,
so it isn't repeatable.

Please help if you can.

Thanks,
Erik J. Long


--
ErikLong
------------------------------------------------------------------------
ErikLong's Profile: http://www.excelforum.com/member.php...o&userid=32627
View this thread: http://www.excelforum.com/showthread...hreadid=524318

Erik,

Take a look at this code - it bases the plot ranges on named ranges within
the worksheet and redefines the worksheet names (so the chart follows the
names), mobing them down (or up, depending on which macro you use) by one row
)the weeks running downeards in a column). The chart plots three ranges, one
for actual hours, one for budget hours and one for cumulative hours, so you
may want to take one or more out, depending on your requirements.
It looks horrendous, but it will make a lot more sense when it's pasted into
a code module!

You'll need to manually set up the chart the first time you use it, creating
the range names specified in the code.

The code also ensures that you can't move the plot range below the last row
containing a week number, or above the first row containing a week number.

Sub HoursGoForwardOneWeek()
'This macro shifts the current X Axis, Actual, Budget and Cumulative
ranges in the HOURS chart FORWARD by one week.
'It ensures that the LAST week of the proposed new plot range is not
lower than the LAST week in XAxislabels.

SheetUnprotect
DefineXAxisDetails

DefineHoursPlotRangeDetails

If LastXAxisLabelRow = HoursLastRow Then
MsgBox ("You can't shift the plot range down any more, as the last
row plotted is the last row in the X-Axis!")
Exit Sub
End If

ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("Hours_XAxis").Offset(1, 0)
ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("Hours_Actual").Offset(1, 0)
ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("Hours_Budget").Offset(1, 0)
ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("Hours_Cumulative").Offset(1, 0)

DefineHoursPlotRangeDetails
SheetProtect

End Sub

Sub HoursGoBackOneWeek()
'This macro shifts the current X Axis, Actual, Budget and Cumulative
ranges in the HOURS chart BACK by one week.
'It ensures that the FIRST week of the proposed new plot range is not
lower than the FIRST week in XAxislabels.

SheetUnprotect
DefineXAxisDetails

DefineHoursPlotRangeDetails

If FirstXAxisLabelRow = HoursFirstRow Then
MsgBox ("You can't shift the plot range up any more, as the first
row plotted is the first row in the X-Axis!")
Exit Sub
End If

ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("Hours_XAxis").Offset(-1, 0)
ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("Hours_Actual").Offset(-1, 0)
ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("Hours_Budget").Offset(-1, 0)
ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("Hours_Cumulative").Offset(-1, 0)

DefineHoursPlotRangeDetails
SheetProtect

End Sub

Sub DefineXAxisDetails()

Set Database = Sheets("Status Report")
Set DBCR = Database.Range("HeaderXAxis").CurrentRegion

DBCR.Offset(1, 0).Resize(DBCR.Rows.Count - 1, 1).Name = "XAxisLabels"
Set XAxisLabels = Database.Range("XAxisLabels")

XAxisLabelsSize = XAxisLabels.Rows.Count 'number of rows in "XAxisLabels"
FirstXAxisLabelRow = XAxisLabels.Cells(1, 1).Row 'row number of FIRST X
axis label
LastXAxisLabelRow = XAxisLabels.Cells(XAxisLabelsSize, 1).Row 'row
number of LAST X axis label
FirstXAxisLabelWeek = Left(XAxisLabels.Cells(1, 1).Value, 2) 'first two
chars of FIRST X axis label
LastXAxisLabelWeek = Left(XAxisLabels.Cells(XAxisLabelsSize, 1).Value,
2) 'first two chars of LAST X axis label

'MsgBox ("Number of X Axis Labels: " & XAxisLabelsSize & vbCrLf & vbCrLf
& _
"First X Axis Label: row " & FirstXAxisLabelRow & " (week " &
FirstXAxisLabelWeek & ")" & vbCrLf & _
"Last X Axis Label: row " & LastXAxisLabelRow & " (week " &
LastXAxisLabelWeek & ")")
End Sub

Sub DefineHoursPlotRangeDetails()
HoursPlotSize = Range("Hours_XAxis").Rows.Count 'number of rows in X
axis of HOURS chart
HoursFirstRow = Range("Hours_XAxis").Cells(1, 1).Row 'row number of
FIRST row in X axis of HOURS chart
HoursLastRow = Range("Hours_XAxis").Cells(HoursPlotSize, 1).Row 'row
number of LAST row in X axis of HOURS chart
HoursFirstWeek = Left(Range("Hours_XAxis").Cells(1, 1).Value, 2) 'first
2 chars of FIRST X axis label (WkNo) of HOURS chart
HoursLastWeek = Left(Range("Hours_XAxis").Cells(HoursPlotSize, 1).Value,
2) 'first 2 chars of LAST X axis label (WkNo)of HOURS
'MsgBox ("Plotting " & HoursPlotSize & " rows" & vbCrLf & vbCrLf & _
"First row: " & HoursFirstRow & " (week " & HoursFirstWeek & ")" &
vbCrLf & _
"Last row: " & HoursLastRow & " (week " & HoursLastWeek & ")")
End Sub

----------------------------------------------------------------------------------------
If you want to redefine the number of rows/weeks your chart plots, use this:

Sub ResizeHours()

SheetUnprotect
DefineXAxisDetails
DefineHoursPlotRangeDetails

Message = "Number of weeks"
Title = "How many weeks' information do you want to plot?"
DefaultSize = 8

NewSize = InputBox(Message, Title, DefaultSize)

If NewSize < 1 Then
Exit Sub
End If

If Not (IsNumeric(NewSize)) Then
Exit Sub
End If

ProposedHoursLastRow = HoursFirstRow + NewSize - 1
'MsgBox ("Modify Hours Graph" & vbCrLf & vbCrLf & _
"Starting at row " & HoursFirstRow & " and continuing for " &
HoursNewSize & _
" rows would finish at row " & ProposedHoursLastRow & vbCrLf & _
"The last X axis label row is " & LastXAxisLabelRow)

If ProposedHoursLastRow LastXAxisLabelRow Then
MsgBox ("You can't extend the plot range this far as it will extend
beyond the bottom of the X Axis!" _
& vbCrLf & "Try moving your plot range back before attempting to
extend it by this number of rows!")
Exit Sub
End If

ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("Hours_XAxis").Resize(NewSize, 1)
ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("Hours_Actual").Resize(NewSize, 1)
ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("Hours_Budget").Resize(NewSize, 1)
ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("Hours_Cumulative").Resize(NewSize , 1)

DefineHoursPlotRangeDetails
SheetProtect

End Sub

----------------------------------------------------------------------------------------
Good luck

Pete





"ErikLong" wrote:


Hey,

I have a spreadsheet containing charts that are linked to cell ranges
within the worksheet. (last four weeks of data).

Every week, we must go in and move the source forward one column
manually.

Is there any way of doing this in VBA. I tried recording a macro and
moving it, but it assigns the new range to a literal location of cells,
so it isn't repeatable.

Please help if you can.

Thanks,
Erik J. Long


--
ErikLong
------------------------------------------------------------------------
ErikLong's Profile: http://www.excelforum.com/member.php...o&userid=32627
View this thread: http://www.excelforum.com/showthread...hreadid=524318

Erik,

Also, be aware that my code contains some references to updating cells on
the worksheet that you probably won't need. Hope it makes sense.

It took me a LONG time to understand what I needed to do, especially the
bits about making sure the named ranges picked up by the chart aren't moved
up or down too far - it DOES work, believe me -I went through too many user
demonstrations for it not to by now..! :-)

Pete




"ErikLong" wrote:


Hey,

I have a spreadsheet containing charts that are linked to cell ranges
within the worksheet. (last four weeks of data).

Every week, we must go in and move the source forward one column
manually.

Is there any way of doing this in VBA. I tried recording a macro and
moving it, but it assigns the new range to a literal location of cells,
so it isn't repeatable.

Please help if you can.

Thanks,
Erik J. Long


--
ErikLong
------------------------------------------------------------------------
ErikLong's Profile: http://www.excelforum.com/member.php...o&userid=32627
View this thread: http://www.excelforum.com/showthread...hreadid=524318

Erik,

Not sure if I included this in my other postings, but here's the code to
reset the named ranges picked up by the chart, so they move to the top of the
range containing your week numbers. Make sure that if you have field headers
above your data ranges that there is at least one blank row separating them
from any other entries in your worksheet. This application was created as a
database, and as such, adheres to the rules for Excel databases.

Sub ResetHours()
'This macro resets the X Axis, Actual, Budget and Cumulative ranges in
the HOURS chart to start from the first
'row of the XAxisLabels range. It requests the number of weeks that are
required to be plotted
'and ensures that this would not exceed the length of the XAxisLabels
range.

SheetUnprotect
DefineXAxisDetails

Message = "Reset Hours Graph"
Title = "Reset Hours Graph"
DefaultSize = 10

NewSize = InputBox(Message, Title, DefaultSize)

If NewSize < 1 Then
Exit Sub
End If

If Not (IsNumeric(NewSize)) Then
Exit Sub
End If

ProposedHoursResetLastRow = Range("HeaderXAxis").Offset(1, 0).Row +
NewSize - 1

'MsgBox ("First row would be: " & Range("HeaderXAxis").Offset(1, 0).Row
& vbCrLf & _
"Last row would be: " & ProposedHoursResetLastRow & vbCrLf & _
"Last X axis label row is: " & LastXAxisLabelRow)

If Range("HeaderXAxis").Offset(1, 0).Row + NewSize LastXAxisLabelRow
Then
MsgBox ("You can't reset the chart to this number of rows, as the
last row plotted would be " & vbCrLf & _
"lower than the last row in the X-Axis!")
Exit Sub
End If

ActiveWorkbook.Names.Add Name:="Hours_XAxis",
RefersTo:=Range("HeaderXAxis").Offset(1, 0).Resize(NewSize, 1)
ActiveWorkbook.Names.Add Name:="Hours_Actual",
RefersTo:=Range("HeaderTotalHours").Offset(1, 0).Resize(NewSize, 1)
ActiveWorkbook.Names.Add Name:="Hours_Budget",
RefersTo:=Range("HeaderBudgetHours").Offset(1, 0).Resize(NewSize, 1)
ActiveWorkbook.Names.Add Name:="Hours_Cumulative",
RefersTo:=Range("HeaderCumulativeHours").Offset(1, 0).Resize(NewSize, 1)
SheetProtect
End Sub

-----------------------------------------------------------------------------------
I'll go home now - good luck!

Pete




"ErikLong" wrote:


Hey,

I have a spreadsheet containing charts that are linked to cell ranges
within the worksheet. (last four weeks of data).

Every week, we must go in and move the source forward one column
manually.

Is there any way of doing this in VBA. I tried recording a macro and
moving it, but it assigns the new range to a literal location of cells,
so it isn't repeatable.

Please help if you can.

Thanks,
Erik J. Long


--
ErikLong
------------------------------------------------------------------------
ErikLong's Profile: http://www.excelforum.com/member.php...o&userid=32627
View this thread: http://www.excelforum.com/showthread...hreadid=524318