Where is the closed workbook that the OP asked about?
A workbook must be open for a chart to change a reference to a worksheet
range within that workbook. An existing reference to a closed workbook is
fine, as long as the reference is not edited.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Kostas" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> The following code is an extract from a program that was extensively
> used for auto-assigning ranges to plots. All you need to do is toggle
> between target and source file to get the data.
>
> Also, please remember that the code is highly dependent on the file
> layout. Please make the necessary change when targeting various sheets
> & files.
>
> Have fun
>
>
>
>
>
> Public Current_Data_Worksheet
> Public Current_Plot_Worksheet
> Public Current_chart_number
> Public current_file_name
>
> Sub v_vs_lnp_plot_range_generation()
> Dim ROW_START As Integer
>
> 'setting up parameters
>
> ROW_START = 14 ' row at which last data row loop will start
> Current_Data_Worksheet = "Step 2 - IsoCompression" ' worksheet at
> which plot data are stored
> Current_Plot_Worksheet = "v vs lnp" ' worksheet at which data is
> stored
> X1_COLUMN = 22
> Y1_COLUMN = 18
> 'X2_COLUMN
> 'Y2_COLUMN
> 'X3_COLUMN
> 'Y3_COLUMN
>
>
>
>
>
>
>
>
>
> current_file_name = ActiveWorkbook.Name
>
> Windows(current_file_name).Activate
> Worksheets(Current_Data_Worksheet).Activate
>
> Call Previous_Range_deletion_v_vs_lnp
>
>
> 'calculating last data row
>
> Worksheets(Current_Data_Worksheet).Activate
> Range("t5").Select
>
>
> Dim CurrentRow As Integer 'Current row being dealt with
>
> CurrentRow = ROW_START 'Start on first row of data
>
>
> 'Find end row
> While Cells(CurrentRow, COLUMN_TIME).Value <> "" 'While the cell
> isn't blank
>
> CurrentRow = CurrentRow + 1 'Move one cell down
>
> Wend
>
> row_end = CurrentRow - 1 'Note last data row
>
>
>
>
>
>
>
>
>
>
> 'graph range loop
>
> Sheets(Current_Plot_Worksheet).Select
> ActiveChart.ChartArea.Select
>
>
>
>
> 'constructiong new set of data ranges
>
>
> Set Site_Name = Worksheets("Initially").Cells(2, 2)
> Set Experiment_date = Worksheets("Initially").Cells(3, 2)
> Set Approximate_Sample_Depth = Worksheets("Initially").Cells(5,
> 5)
> Set sample_state = Worksheets("Initially").Cells(4, 5)
>
>
> Xvalue = "='" & Current_Data_Worksheet & "'!R13C" & X1_COLUMN &
> ":R" & row_end & "C" & X1_COLUMN
>
> Yvalue = "='" & Current_Data_Worksheet & "'!R13C" & Y1_COLUMN &
> ":R" & row_end & "C" & Y1_COLUMN
>
> Legend_name = sample_state & " - " & Site_Name & " - " &
> Approximate_Sample_Depth & "m - " & Experiment_date
>
> Debug.Print Xvalue
> Debug.Print Yvalue
> Debug.Print Legend_name
>
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(1).XValues = Xvalue
> ActiveChart.SeriesCollection(1).Values = Yvalue
> ActiveChart.SeriesCollection(1).Name = Legend_name
>
>
>
>
> ' Worksheets(Current_Data_Worksheet).Activate
>
>
>
>
> End Sub
>
> Sub Previous_Range_deletion_v_vs_lnp()
>
> Dim count As Integer
>
>
> Sheets(Current_Plot_Worksheet).Select
> ActiveChart.ChartArea.Select
>
>
>
> 'deliting previous graph ranges
>
> i = ActiveChart.SeriesCollection.count
>
> Do While i > 0
>
> ActiveChart.SeriesCollection(1).Delete
>
> i = i - 1
>
> Loop
>
>
>
>
> End Sub
>
Similar Threads
