VBA代码为excel。如何在单独的表单中创建图表？ [英] VBA Code for excel. How to create graphs in separated sheets?
问题描述
我可以有一个具有两种列的Excel文档：可以被分组为6或可以被分组为7的列的列。
前2个图片表示如何接收到excel文档。
我要做的是：
步骤1。 复制列A并将其放在每组6或7列之前，并插入一个空列（如图3所示）。
步骤2 为每张新表中创建的新组创建一个图表（例如，如果我有100组列，我想拥有100张图，单张图中的每个图）
问题是： 如何将每张图表放在单独的表格中？
如果需要，第一张表的名称是HOOD
我写的代码可以做第1步，并创建图，但问题是我不能把每个图表放在一张单页上。
我可以做第1步，从第2步开始，我只能创建图形，但是不能将每个图形都放在一张新表格中。
Sub Macro_Linearity_Plot（）
Dim pas As Integer
Dim val As Integer
Dim lCol As Integer
Dim i As Integer
Dim uCol As Integer
'定义列数。它可以是6或7列。
lCol = Cells（1，Columns.Count）.End（xlToLeft）.Column
val = Range（A1）。value
pas = val + 2
'插入2个新的空列
对于colx = pas至lCol步骤pas
列（colx）.Insert Shift：= xlToRight
列（colx）.Insert Shift：= xlToRight
下一个
'插入列号1
对于colx = pas + 1 to lCol步骤pas
表（HOOD）。列（1）.Copy
表（HOOD）。列（colx）.PasteSpecial xlPasteValues
下一个
'为最后一步创建的每一组列生成一个图表
uCol = Cells（1，Columns.Count）.End（xlToLeft）.Column
For i = -1到uCol步骤pas
范围（单元格（2，i + 2），单元格（121，i + pas））。选择
ActiveSheet.Shapes.AddChart.Select
ActiveChart .SetSourceData Source：= Range（Cells（2，i + 2），Ce lls（121，i + pas））
ActiveChart.ChartType = xl3DArea
下一个
End Sub
感谢：）
UPDATED
新代码是：
Sub Macro_Linearity_Plot（）
Dim pas As Integer
Dim val As Integer
Dim lCol As Integer
Dim i As Integer
Dim uCol As Integer
'定义列数。它可以是6或7列。
lCol = Cells（1，Columns.Count）.End（xlToLeft）.Column
val = Range（A1）。value
pas = val + 2
'插入2个新的空列
对于colx = pas至lCol步骤pas
列（colx）.Insert Shift：= xlToRight
列（colx）.Insert Shift：= xlToRight
下一个
'插入列号1
对于colx = pas + 1 to lCol步骤pas
表（HOOD）。列（1）.Copy
表（HOOD）。列（colx）.PasteSpecial xlPasteValues
下一个
'为最后一步创建的每一组列生成一个图表
uCol = Cells（1，Columns.Count）.End（xlToLeft）.Column
For i = -1到uCol步骤pas
范围（单元格（2，i + 2），单元格（121，i + pas））。选择
ActiveSheet.Shapes。 AddChart.Select
ActiveChart.SetSourceData Source：= Range（Cells（2 ，i + 2），Cells（121，i + pas））
ActiveChart.ChartType = xl3DArea
xx = 1'只是为了识别图形顺序
ActiveChart.Location Where ：= xlLocationAsNewSheet，Name：=Chart& xx
'将图表和图表计算到最后
ws = ThisWorkbook.Worksheets.Count
cht = ThisWorkbook.Charts.Count
表格（Chart& xx）.Move After：= Sheets（ws + cht）
xx = xx + 1
下一个
End Sub
但有一些错误：
当您使用不合格范围时，遇到的是典型的。不合格范围是指每次插入新工作表时更改的活动工作表，所以您的代码在第一个循环后开始搞砸。
起初我修复了在每个循环之后，由重新激活 ，但是我更愿意完全重写代码，从而不会引用不合格的范围，除了一些其他的修复程序。
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）值
pas = val + 2
'插入一个空列和一列A
对于colx = pas对于lCol步骤pas
.Columns（colx）.Insert Shift：= xlToRight
.Columns（colx）.Insert Shift：= xlToRight
.Columns（1）.copy .Columns（colx + 1）
Next
Application.CutCopyMode = False
'为每一列生成图表并将其移动到工作簿
lCol = .Cells（1，Columns.Count）的末尾。结束（xlToLeft）.Column
对于i = -1到lCol步骤pas
设置ch = ActiveWorkbook.Charts.Add'< ~~在自己的新表中添加图表
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）
下一个
结束
End Sub
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.
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:
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
这篇关于VBA代码为excel。如何在单独的表单中创建图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
