Carina33
12-13-2006, 10:17 AM
Hi :hi:
I have a problem with a line chart. I have some data in one sheet that I use a macro for to copy to another sheet. I then have a line chart that I want to get updated using the data copied to the sheet. I don't want the chart to plot the empty cells but it seems that in a line chart the line will move to 0 if the next cell is empty.
I have this code to copy the data which does not distinguish if the cell is empty or not, it just copies the entire table (because I can't get that part to work...)
Sub Copiera()
Dim Source As Range, cel As Range
Dim Tgt As Range
Set Source = Sheets("Data").Range("B70:C84")
Sheets("Stat").Activate
Range("B32:C46").Select
Selection.ClearContents
Set Tgt = Sheets("Stat").Cells(47, 2).End(xlUp).Offset(1).Resize(, 2)
Source.Copy
Tgt.PasteSpecial Paste:=xlValues
End Sub
The actual line chart is located in the sheet where I have copied the data to but I have not recorded a macro for the chart because I am not sure of the code for this. I have just used the chartwizard to create the chart and was hoping this is good enough(?).
I have tried to go to Tools-Options-Chart-Plot empty cells as, but it does not help.
Is it possible to get the line in the chart to show only up until those cells that includes a value (i.e not the full range b32:C46)? In order to do this do I have to create the chart in the vba code window? I need the macro to copy the cells to the sheet which should display the chart because the values depends on what the user inputs each time and will hence vary.
I am sorry I can't provide any code for the chart but I am new to VBA so I don't know how to do this.
Thanks in advance
Carina
I have a problem with a line chart. I have some data in one sheet that I use a macro for to copy to another sheet. I then have a line chart that I want to get updated using the data copied to the sheet. I don't want the chart to plot the empty cells but it seems that in a line chart the line will move to 0 if the next cell is empty.
I have this code to copy the data which does not distinguish if the cell is empty or not, it just copies the entire table (because I can't get that part to work...)
Sub Copiera()
Dim Source As Range, cel As Range
Dim Tgt As Range
Set Source = Sheets("Data").Range("B70:C84")
Sheets("Stat").Activate
Range("B32:C46").Select
Selection.ClearContents
Set Tgt = Sheets("Stat").Cells(47, 2).End(xlUp).Offset(1).Resize(, 2)
Source.Copy
Tgt.PasteSpecial Paste:=xlValues
End Sub
The actual line chart is located in the sheet where I have copied the data to but I have not recorded a macro for the chart because I am not sure of the code for this. I have just used the chartwizard to create the chart and was hoping this is good enough(?).
I have tried to go to Tools-Options-Chart-Plot empty cells as, but it does not help.
Is it possible to get the line in the chart to show only up until those cells that includes a value (i.e not the full range b32:C46)? In order to do this do I have to create the chart in the vba code window? I need the macro to copy the cells to the sheet which should display the chart because the values depends on what the user inputs each time and will hence vary.
I am sorry I can't provide any code for the chart but I am new to VBA so I don't know how to do this.
Thanks in advance
Carina