0

I'm attempting to automate the 2 charts using VBA. After multiple attempts, I'v have finally put together some code that works for only one graph and gives Time run error '800401a8' but chart displays in same excel from which I'm taking input instead of targetSheet. Here is some code I've tried -

'graph1................

Dim graphSheet As Worksheet
        Set graphSheet = targetWorkbook.Worksheets(2)

        graphSheet.Activate


    recordLayerSheet.Activate
    recordLayerSheet.ShowAllData
    recordLayerSheet.Range("R:R,F:F,F:F,R:R").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=recordLayerSheet.Range( _
        "'Record Layer'!$R:$R,'Record Layer'!$R:$R" _
        )
    ActiveChart.ChartType = xlLine
    ActiveChart.Copy
    
    graphSheet.Activate
    targetSheet.Range(I2).PasteSpecial
'graph2.............
Dim graphSheet As Worksheet
        Set graphSheet = targetWorkbook.Worksheets(2)

        graphSheet.Activate


    recordLayerSheet.Activate
    recordLayerSheet.ShowAllData
    recordLayerSheet.Range("S:S,F:F,F:F,S:S").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=recordLayerSheet.Range( _
        "'Record Layer'!$S:$S,'Record Layer'!$S:$S" _
        )
    ActiveChart.ChartType = xlLine
    ActiveChart.Copy
    
    graphSheet.Activate
    targetSheet.Range(K2).PasteSpecial

I'm guessing it's something really simple. Any Help would be greatly appreciated. Thanks!

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Sanna
  • 1
  • Unless you have a variable called `I2` last line should be `targetSheet.Range("I2").PasteSpecial` with quotes around `I2` – CDP1802 Mar 02 '21 at 22:12
  • `ActiveSheet.Shapes.AddChart` adds the chart to the active sheet, which is `recordLayerSheet`. You should try very hard to avoid using Activate/Select etc in your code: worth looking over this post and trying to apply it to your code https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Tim Williams Mar 02 '21 at 22:36

0 Answers0