Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a workbook in which I have several charts in three worksheets. In a different wooksheet I have all of the data for the charts. I created a macro that would automatically move the data over by one cell so that each month, I will only have 13 months of rolling data.

    Usually my charts would update with the new 13 month's worth of data. Now the charts will not update with the new data unless I redo the source data even though the data stays in the same cell/row each month. I dont' think this would make a difference but the only thing that I can think of that is different would be the macro written to do the copy/paste special values. Below is the code. Do you have any ideas as to why the charts will not update unless I do the source data again for each chart?

    Thanks
    [codebox]
    Sheets("All Other Regional Charts").Select
    Range("F48:Q67").Select
    Selection.Copy
    Range("E48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("Z48:AK67").Select
    Selection.Copy
    Range("Y48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AT48:BE67").Select
    Selection.Copy
    Range("AS48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("BN48:BY67").Select
    Selection.Copy
    Range("BM48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("CH48:CS67").Select
    Selection.Copy
    Range("CG48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("DB48M67").Select
    Selection.Copy
    Range("DA48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("DV48:EG67").Select
    Selection.Copy
    Range("DU48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("EP48:FA67").Select
    Selection.Copy
    Range("EO48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Western Div Charts").Select
    Range("F48:Q67").Select
    Selection.Copy
    Range("E48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("Z48:AK67").Select
    Selection.Copy
    Range("Y48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AT48:BE67").Select
    Selection.Copy
    Range("AS48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("BN48:BY67").Select
    Selection.Copy
    Range("BM48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("CH48:CS67").Select
    Selection.Copy
    Range("CG48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("DB48M67").Select
    Selection.Copy
    Range("DA48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Eastern Div Charts").Select
    Range("F48:Q67").Select
    Selection.Copy
    Range("E48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("Z48:AK67").Select
    Selection.Copy
    Range("Y48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AT48:BE67").Select
    Selection.Copy
    Range("AS48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("BN48:BY67").Select
    Selection.Copy
    Range("BM48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("CH48:CS67").Select
    Selection.Copy
    Range("CG48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Data").Select
    Range("C5:N28").Select
    Selection.Copy
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("n6:N28").Select
    Selection.ClearContents
    Range("C34:N57").Select
    Selection.Copy
    Range("B34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("n35:N57").Select
    Selection.ClearContents
    Range("C64:N87").Select
    Selection.Copy
    Range("B64").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("n65:N87").Select
    Selection.ClearContents
    Range("R5:AC28").Select
    Selection.Copy
    Range("Q5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("ac6:ac28").Select
    Selection.ClearContents
    Range("R34:AC57").Select
    Selection.Copy
    Range("Q34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("ac35:ac57").Select
    Selection.ClearContents
    Range("R91:AC114").Select
    Selection.Copy
    Range("Q91").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("ac92:ac114").Select
    Selection.ClearContents
    Range("R122:AC145").Select
    Selection.Copy
    Range("Q122").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("ac123:ac145").Select
    Selection.ClearContents
    Range("R152:AC177").Select
    Selection.Copy
    Range("Q152").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("ac153:ac177").Select
    Selection.ClearContents
    Range("R181:AC204").Select
    Selection.Copy
    Range("Q181").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("ac182:ac204").Select
    Selection.ClearContents
    Sheets("Consolidated Charts").Select
    Range("g28:r47").Select
    Selection.Copy
    Range("f28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub
    [/codebox]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Has calculation been set to Manual instead of Automatic?

    BTW, code such as

    Range("F48:Q67").Select
    Selection.Copy
    Range("E48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    can be simplified to

    Range("F48:Q67").Copy
    Range("E48").PasteSpecial Paste:=xlPasteValues

    This is not only shorter, but more efficient too since the source range doesn't have to be selected before copying it.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The macrto starts with the line Sheets("All Other Regional Charts").Select but there is no such sheet.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I removed enough of the code to make it work, but then the chart I looked at was updated immediately, so I can't reproduce the problem.

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791228' date='28-Aug-2009 23:22']I removed enough of the code to make it work, but then the chart I looked at was updated immediately, so I can't reproduce the problem.[/quote]

    What part of the code did you remove? Just your prior suggestion?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I removed the part that refers to sheets that are not present in the workbook that you attached.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I can't reproduce the problem in this version either. I'd double check that calculation is set to Automatic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •