# Working with 3 sheets - syntax to specify the sheet that holds the chart data and format data

#### nicksoph

##### Board Regular
Hi

I have a workbook which has 3 sheets;
sheet 1 holds the data,
sheet 2 has only one chart which is a smoothed line scatter chart based on the data in sheet 1, and
sheet 3 has three columns which specify RGB values, which I would like to apply to the lines between the the scatter points in sheet 2 so that Row 1's RGB values are used to colour the line between Points 1 and 2, row 2's between points 2 and 3....

I have used the following code to do something similar when the data, chart and colours were all on 1 sheet but am unable to find the syntax for specifying the sheets/chart.

I would like to be able to embed the code in a button on sheet 3 so on clicking it the colour formatting was applied and wonder if someone might oblige with some direction on working with multiple sheets.

Thanks
nicksoph

Code:
``````Sub colplot()

Dim i As Double, plotcounter As Double

plotcounter = 1 ' The number of the series to plot
'
For i = 1 To 20 ' first 20 rows in sheet
ActiveChart.SeriesCollection(plotcounter).Points(i).Border.Color = RGB(Cells(i, 3), Cells(i, 4), Cells(i, 5))

Next i
End Sub``````

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try (untested):

Code:
``````Sub colplot()
Dim i As Double, plotcounter As Double
Dim ws As Worksheet
Set ws = Worksheets("Sheet3")
plotcounter = 1 ' The number of the series to plot
'
For i = 1 To 20 ' first 20 rows in sheet
Worksheets("Sheet2").ChartObjects(1).Chart.SeriesCollection(plotcounter).Points(i).Border.Color = RGB(ws.Cells(i, 3), ws.Cells(i, 4), ws.Cells(i, 5))
Next i
End Sub``````

Hi and thanks for the quick reply - just tried and it falls over immediately on the line;
Set ws = Worksheets("Sheet9")
with the error - Runtime error '9': Subscript out of range.

I simplified my description earlier and think that may have been a fault - the workbook actually has 10 sheets which each have only one chart on - several sheets have been deleted but the numbers of the sheets of interest and their tab names as shown in the project window are as follows

1. Sheet1 (Calcs) - the data
2. Sheet16 (1 & 2) - the plot
3. Sheet9 (gradient) - the colours

Translating your answer to the actual sheets being used I put the following into the ThisWorkbook section of the VBA project in the workbook;

Code:
``````Sub colplot()
Dim i As Double
Dim plotcounter As Double

Dim ws As Worksheet

Set ws = Worksheets("Sheet9")
plotcounter = 1 ' The number of the series to plot'
For i = 1 To 20 ' first 20 rows in sheet
Worksheets("Sheet16").ChartObjects(1).Chart.SeriesCollection(plotcounter).Points(i).Border.Color = RGB(ws.Cells(i, 3), ws.Cells(i, 4), ws.Cells(i, 5))
Next i

End Sub``````

I am not any sort of coder so may be doing something wrong rather than there being any error in the code. Any advice appreciated

Last edited:
The code uses worksheet Names (eg gradient) not CodeNames.

I did try that but it came back with - Runtime error 13: Type mismatch on - Set ws = Worksheets("gradient")

That's not possible if ws is declared as Worksheet.

just REM'd out the Dim ws statement and it seems to work!

Hi Andrew
think it was user error - I put an S on the Dim statement - Dim ws as 'Worksheets' rather than 'Worksheet'. Sorry to have wasted your time and thank you for the code. It works smashingly.

Many thanks
nick

Replies
2
Views
388
Replies
2
Views
593
Replies
1
Views
598
Replies
2
Views
301
Replies
2
Views
370

1,219,941
Messages
6,151,082
Members
451,007
Latest member
gianmatt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back