Chart updates from rolling columns on Excel 2010

Topic

New Reply

I have a spreadsheet that I update regularly. On one sheet, I have about 16 rows and columns for each week of the year.
There is a chart associated with it that shows all of the rows and then just 4 of the week columns. Each week, I hide the current first column and then unhide the new weeks column.
This was all working fine until recently and now when I change the visible week columns, the chart does not remove the removed week and add the added week.
I inherited this spreadsheet, so I do not know how it was set up to do this.
My attempts to fix it were not successful and it only shows the specific weeks I selected in the data choices. When I make the change, it loses the now hidden previous week and does not include the new unhidden week.
How do I set this up to work again?
Thanks,
Jeff

Reply | Quote

Replies

Can you attach a cleaned up version with no proprietary info in it so we can examine it?

Steve

Reply | Quote

Steve,
Here is the page I am having issues with. I have left the sheet with only 4 weeks visible. The rest of the preceding and following week columns are hidden, so you can see what I do with this.
So for this coming week, I would hide the first visible column, then unhide the rest of the year, then hide all of them except the column for 3/17/14.
Jeff

Reply | Quote

JWThau,

Seems like you are going through a lot of trouble updating this workbook weekly. Here is some code that will let you scroll through a 1 month period (by weekly increments) using spin buttons while your graph updates automatically.

HTH,
Maud

36533-Stats

Code:

Public Sub UpdateNext()
Application.ScreenUpdating = True
For I = 2 To 49
If Cells(2, I).EntireColumn.Hidden = False Then
    Cells(2, I).EntireColumn.Hidden = True
    Cells(2, I + 4).EntireColumn.Hidden = False
    Exit For
End If
Next I
Application.ScreenUpdating = False
End Sub

Public Sub UpdatePrevious()
Application.ScreenUpdating = True
For I = 3 To 53
If Cells(2, I).EntireColumn.Hidden = False Then
    Cells(2, I – 1).EntireColumn.Hidden = False
    Cells(2, I + 3).EntireColumn.Hidden = True
    Exit For
End If
Next I
Application.ScreenUpdating = False
End Sub

Reply | Quote

Maud,
That is very cool and thank you so much. I thought about taking credit for this at work ;), but that wouldn’t be fair to the great little function you gave me.
I am still curious about why this used to work and then stopped. Learning those kinds of things makes me more functional and I enjoy figuring it out.
Jeff

Reply | Quote

Jeff,

Reverse the True/False in the 4 lines Application.ScreenUpdating= and the transition will run very smoothly. Had a “duh” moment there. Take credit for it, this one is on me!

Maud

Reply | Quote

Okay, did that, but I have a question here.
I added the code to my macros for that workbook, but I don’t get the buttons.
How do I copy over the spinbuttons code?

Reply | Quote

Go to Developer tab> insert> More Controls> Scroll down to SpinButton> OK> drag cross hair to draw the control. Note the following event procedures for the control in the sheet’s module that call the routines that do the work.

36541-spin

Code:

Private Sub SpinButton1_SpinDown()
UpdatePrevious
End Sub

Private Sub SpinButton1_SpinUp()
UpdateNext
End Sub

If you do not find it, you could PM me I will give you my email address so I could add it for you.

Maud

Reply | Quote

Maud,
Thanks, I figured it out. Works perfectly.
Thanks so much for the help.
Jeff

Reply | Quote