Confused on VBA copy destination

0 votes

I'm trying to make a chart using chosen data from one sheet to create a totally new sheet that displays the chart. I'm extremely new to VBA.

My current code is:

Dim rng As Range
Set rng = Selection

    ' add a chart and select it - Selection becomes this chart
    ActiveSheet.Shapes.AddChart.Select
    
    'paste selection into cell C4 of Sheet2
    rng.Copy Destination:=Sheets("Sheet2").Range("C4")
    
    Sheets("Sheet2").Activate
   
    With ActiveChart
        'Chart type is Clustered Bar chart
        .ChartType = xlBarClustered
      
        'Set a chart title, located at the top of the chart
        .SetElement msoElementChartTitleAboveChart
      
        'Assign the content of cell B1 to the title of the chart
        .chartTitle.Text = Worksheets("Sheet1").Range("B1").Value
      
        'Move the chart to a new sheet 
        .Location Where:=xlLocationAsNewSheet, Name:="Sheet2"
    End With

It keeps showing a run-time error 13 with the Set rng = Selection and then sometimes an out-of-range error pops up around the copy. destination.

My hypothesis is that the out-of-range problem appears because "Sheet2" is not yet a sheet, however, when I create Sheet2, a method SetElement of Chart object failed error appears. It doesn't appear to make a difference whether the Copy Destination lines are within or outside of the ActiveChart.

Feb 20, 2023 in Others by narikkadan
• 63,640 points
855 views

1 answer to this question.

0 votes

Try this:

Sub so75496418AddChart()

Dim rng As Range
Dim strTypeName As String, strNewSheetName As String

  strTypeName = TypeName(Selection)

  If strTypeName = "Range" Then

    Set rng = Selection

    If WorksheetFunction.CountA(rng) = 0 Then
      MsgBox "No data in range."
      Set rng = Nothing
      Exit Sub
    End If

    'paste selection into cell C4 of Sheet2
    'rng.Copy Destination:=Sheets("Sheet2").Range("C4")

    strNewSheetName = "Sheet" & (Sheets.Count + 1)

    With ActiveSheet.Shapes.AddChart.Chart
        'Chart type is Clustered Bar chart
        .ChartType = xlBarClustered

        .SetSourceData Source:=rng

        'Set a chart title, located at the top of the chart
        .SetElement msoElementChartTitleAboveChart

        'Assign the content of cell B1 to the title of the chart
        .ChartTitle.Text = ActiveSheet.Range("B1").Value

        'Move the chart to a new sheet
        .Location Where:=xlLocationAsNewSheet, Name:=strNewSheetName

    End With

    ' move the sheet as the last sheet:
    Sheets(strNewSheetName).Move after:=Sheets(Sheets.Count)

    Set rng = Nothing

  Else
    MsgBox "Current selection is not a range, but a " & strTypeName & "."
  End If

End Sub
answered Feb 20, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

How To Copy/Cut Row of Data Based on TRUE/FALSE Condition [Excel VBA]

Solution Loop through the rows on the Price ...READ MORE

answered Feb 4, 2023 in Others by narikkadan
• 63,640 points
902 views
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,640 points
2,284 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,039 views
0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,640 points
2,862 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
19,877 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
915 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,640 points
761 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,640 points
877 views
0 votes
1 answer
0 votes
1 answer

VBA runtime error 1004 when copy and pasting

Try this: Sub CopyWorksheet() Dim ...READ MORE

answered Feb 23, 2023 in Others by Kithuzzz
• 38,000 points
1,073 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP