0

I have a question regarding how to create plots (chart) automatically using vba code. I can have an excel document with two kind of columns: columns which can be grouped in 6 or columns which can be grouped in 7. The first 2 pictures represents how I receive the excel document.

What I have to do is:

Step 1. to copy column A and put it before every group of 6 or 7 columns and insert also a empty column like in picture 3.

Step 2. to create a graph for every new group created in a new sheet (for example if I have 100 groups of columns I want to have 100 sheets of plots. every plot on a single sheet)

The question is: How to put every graph in separated sheets?

If you need, the name of the first sheet is "HOOD"

The code written by me can do step 1 and also creates plots, but the problem is I cannot put every graph on a single sheet.

I can do Step 1 and from Step 2 I can only create the graphs but I cannot put every graph in a new sheet.

6columns 7columns using vba code what i obtained

Sub Macro_Linearity_Plot()

        Dim pas As Integer
        Dim val As Integer

        Dim lCol As Integer
        Dim i As Integer
        Dim uCol As Integer


        ' define the numbers of columns. it can be 6 or 7 columns.

        lCol = Cells(1, Columns.Count).End(xlToLeft).Column
        val = Range("A1").Value
        pas = val + 2


        ' insert 2 new empty columns

        For colx = pas To lCol Step pas

        Columns(colx).Insert Shift:=xlToRight
        Columns(colx).Insert Shift:=xlToRight
        Next



        ' insert column number 1

        For colx = pas + 1 To lCol Step pas
        Sheets("HOOD").Columns(1).Copy
        Sheets("HOOD").Columns(colx).PasteSpecial xlPasteValues
        Next

        ' for every group of columns created at the last step generate a chart

        uCol = Cells(1, Columns.Count).End(xlToLeft).Column
        For i = -1 To uCol Step pas
        Range(Cells(2, i + 2), Cells(121, i + pas)).Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=Range(Cells(2, i + 2), Cells(121, i + pas))
        ActiveChart.ChartType = xl3DArea
        Next



        End Sub

Thanks :)

UPDATED

The new code is: 

    Sub Macro_Linearity_Plot()

        Dim pas As Integer
        Dim val As Integer

        Dim lCol As Integer
        Dim i As Integer
        Dim uCol As Integer


        ' define the numbers of columns. it can be 6 or 7 columns.

        lCol = Cells(1, Columns.Count).End(xlToLeft).Column
        val = Range("A1").Value
        pas = val + 2


        ' insert 2 new empty columns

        For colx = pas To lCol Step pas

        Columns(colx).Insert Shift:=xlToRight
        Columns(colx).Insert Shift:=xlToRight
        Next



        ' insert column number 1

        For colx = pas + 1 To lCol Step pas
        Sheets("HOOD").Columns(1).Copy
        Sheets("HOOD").Columns(colx).PasteSpecial xlPasteValues
        Next

        ' for every group of columns created at the last step generate a chart

        uCol = Cells(1, Columns.Count).End(xlToLeft).Column
        For i = -1 To uCol Step pas


        Range(Cells(2, i + 2), Cells(121, i + pas)).Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=Range(Cells(2, i + 2), Cells(121, i + pas))
        ActiveChart.ChartType = xl3DArea
          xx = 1  'Just to identify the Graph order

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart" & xx

'Count the sheets and Charts for moving Chart to the end
ws = ThisWorkbook.Worksheets.Count
cht = ThisWorkbook.Charts.Count

Sheets("Chart" & xx).Move After:=Sheets(ws + cht)

xx = xx + 1


        Next



        End Sub

But there are some errors:

error debug

Improve this question
3
  • 1
    You could push the graph after creating to an extra sheet. Just do it once manually and record it. Should give you the trick Commented Dec 26, 2015 at 12:59
  • OK. I will try. Thanks for the trick Commented Dec 26, 2015 at 13:01
  • Try: ThisWorkbook.Sheets("Chart" & xx).Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Commented Dec 28, 2015 at 18:33

2 Answers 2

Reset to default
1
+50

The problem you encountered is typical when you work with unqualified ranges. Unqualified ranges refer to the active worksheet, which changes every time you insert a new worksheet, so your code starts messing up after the first loop.

At first I had fixed your code by "reactivating" the sheet HOOD after each loop, but I preferred to completely re-write your code so that it never refers to unqualified ranges, in addition to a few other fixes.

Sub Macro_Linearity_Plot()
    Dim pas As Integer, val As Integer, lCol As Integer, i As Integer, ch As Chart

    With Sheets("HOOD")
        lCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        val = .Range("A1").Value
        pas = val + 2

        ' insert an empty column and a copy of column A
        For colx = pas To lCol Step pas
            .Columns(colx).Insert Shift:=xlToRight
            .Columns(colx).Insert Shift:=xlToRight
            .Columns(1).copy .Columns(colx + 1)
        Next
        Application.CutCopyMode = False

        ' for every group of columns generate a chart and move it to end of Workbook
        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        For i = -1 To lCol Step pas
            Set ch = ActiveWorkbook.Charts.Add '<~~ add a chart in own new sheet
            ch.ChartType = xl3DArea
            ch.SetSourceData .Range(.Cells(2, i + 2), .Cells(121, i + pas))
            ch.name = "Chart" & CInt(1 + (i + 2) / pas)
            ch.Move , ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        Next
    End With
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Hi! Thanks for your answer. It seems that is a problem. I have the error number 9 - runtime error. After debug it, I have the following yellow line: "With ThisWorkbook.Sheets("HOOD")". Thanks!
@luli That would mean that the workbook containing the macro does not have a worksheet named "HOOD". Are you working with two open workbooks?
I have a workbook where is saved the macro and another workbook where I have the information which must be prepared. In the workbook where I have the information which must be prepared I press ALT+F8 and then RUN the macro. Thanks!
Ah. That was not stated in your OP. Anyway, you can try With Sheets("HOOD") (Hopefully "HOOD" will be on the Active Workbook)
Now, I have obtained an error at this line: " ch.SetSourceData .Range(.Cells(2, i + 2), .Cells(121, i + pas))" Runtime error: -2147467259. thanks!
|
1

I have looked and changed a number of items in your original script.

my bad on the setting of the initial xx, it needs to be before the loop otherwise it will always be 1.

Some changes I made, and I am sure there are better ways as well: The assignment of the last Column; Make sure you refer to the specific sheet you want to select a group of cells etc.

 Sub Macro_Linearity_Plot()

    Dim pas As Integer
    Dim val As Integer
    Dim lCol As Integer
    Dim i As Integer
    Dim uCol As Integer

    ' define the numbers of columns. it can be 6 or 7 columns.

    'You lCol script was worn got determine the last Column
    With ActiveSheet.UsedRange
        lCol = .Columns(.Columns.Count).Column
    End With
    'lCol = Cells(1, Columns.Count).End(xlToLeft).Column

    val = Range("A1").Value
    pas = val + 2

    ' insert 2 new empty columns

    For colx = pas To lCol Step pas
        Sheets("HOOD").Columns(colx).Insert Shift:=xlToRight
        Sheets("HOOD").Columns(colx).Insert Shift:=xlToRight
    Next

    ' insert column number 1

    For colx = pas + 1 To lCol Step pas
        Sheets("HOOD").Columns(1).Copy
        Sheets("HOOD").Columns(colx).PasteSpecial xlPasteValues
    Next

    ' for every group of columns created at the last step generate a chart

    uCol = Cells(1, Columns.Count).End(xlToLeft).Column

    xx = 1  'Just to identify the Graph order

    For i = -1 To uCol Step pas

        'Need top reselect the "HOOD" sheet for the range selection
        ActiveWorkbook.Sheets("HOOD").Select

        Sheets("HOOD").Range(Cells(2, i + 2), Cells(121, i + pas)).Select

        ActiveWorkbook.Sheets("HOOD").Shapes.AddChart.Select

        ActiveChart.SetSourceData Source:=Range(Cells(2, i + 2), Cells(121, i + pas))
        ActiveChart.ChartType = xl3DArea

        ChartName = "Graph Group " & xx
        ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=ChartName

        'Count the sheets and Charts for moving Chart to the end
        ws = ThisWorkbook.Worksheets.Count
        cht = ThisWorkbook.Charts.Count

        Sheets(ChartName).Move After:=Sheets(ws + cht)

        xx = xx + 1

    Next i

End Sub
Improve this answer

3 Comments

I have made the change, but there are some errors. I have made an updated to see the problems. Thanks for your help!
I put in the code the new lines from you, but I have still an error. When I press debug. the yellow line is: "Sheets(ChartName).Move After:=Sheets(ws + cht)" and also the code produce me only one sheet even if it must produce 3 sheets.
I try it, but there is a problem with the line: "Sheets(ChartName).Move After:=Sheets(ws + cht)". Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.