Data and chart on separate sheets

[dhayesdh]

A question, if I may: my data, and a chart of the data, is in sheet 7 of a workbook. I have a macro that redraws the chart as the data changes. (I set some cells to generate random numbers on 'calculate' command in the macro) . The macro references sheet 7 and the data. For viewing purposes, however, I copied the chart to sheet 8 so that I could increase its size. Now, when I'm in sheet 8 and I run the macro I get a Run-time error '1004': Select method of Range class failed. My guess is that, since sheet references in the macro are to sheet 7, that the macro can't execute when I am looking at sheet 8. Is there any simple way to get the macro to execute when I'm looking at sheet 8 and all the data changes are happening in sheet 7? Any help or advice would be appreciated. Thanks.
Donald Hayes
Deleted by Moderator

 

[texasaswang]

Please post sample code and sample dataset. Also, are you using named ranges to chart?

 

[Peter_SSs]

dhayesdh

Welcome to MrExcel!

I have modified your post. For the reason, please refer to point 19 of the Posting Guidelines

 

[dhayesdh]

Data and chart on separate sheets
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Here is a simplified version of my code, but the issue/problem is the same. The data and code is set up on Sheet 1. The data is 4 cells (A1:A4). Each cell generates a random number =<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace><st1:stockticker>RAND</st1:stockticker></st1lace>()*(10-1)+1. A simple scatter chart on Sheet 1 plots the data which I copy as values only to A7:A10, and the chart updates each time the macro is executed. I can execute from the VBA editor or a custom button/toolbar to which I have assigned the macro. The problem is that if I copy the chart to sheet 2, and activate sheet 2 for viewing purposes, the macro produces a 400 error if executed from the button, and a Run-time error ‘1004’ if executed from the VBA editor. In other words, the macro does not execute when I am looking at sheet 2. I even copied the code into the sheet 2 space for code in the VBA editor, and tried to execute from there, while looking at sheet 2. Nothing. No error, but also neither sheet 1 updated nor sheet 2. Therefore, in conclusion, there seems to be no connection between sheet 1 and sheet 2 when it comes to this macro. Can someone explain what is going on? What am I missing? Thanks in advance.

Public ro, ro1, col
Sub mrexcel()
'
' mrexcel Macro
' Macro recorded <st1:date Month="4" Day="4" Year="2011">4/4/2011</st1:date> by administrator
<o:p> </o:p>
ro = 1
ro1 = 7
    col = 1
    Calculate
line1:       Cells(ro, col).Select
    Selection.Copy
    Cells(ro1, col).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ro1 = ro1 + 1
    ro = ro + 1
    If ro > 4 Then
    End
    Else
    End If
    GoTo line1
End Sub

<o> </o>
<o> </o>

 

[texasaswang]

OK. Because you moved it, you need to add additional code to your macro. The first Sheets line will move you back to the page for the macro to run correctly. The one at the end will move you back to your chart page. Modify the "Sheet 1/2" to fit your usage.

-Tex

Public ro, ro1, col
Sub mrexcel()
'
' mrexcel Macro
' Macro recorded <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:date Year="2011" Day="4" Month="4">4/4/2011</st1:date> by administrator
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Sheets("Sheet 1").Select
ro = 1
ro1 = 7
    col = 1
    Calculate
line1:       Cells(ro, col).Select
    Selection.Copy
    Cells(ro1, col).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ro1 = ro1 + 1
    ro = ro + 1
    If ro > 4 Then
    End
    Else
    End If
    GoTo line1
    Sheets("Sheet 2").Select

End Sub