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