|
VBA: Вставка и форматирование графиков. Как это cделать? |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
— вот я и подозреваю, что 
|
Alejandro67 Пользователь Сообщений: 19 |
#1 16.10.2014 01:08:09 Уважаемые, подскажите как изменить код, что бы диаграммы строились в определенном месте видимой области листа. или же в определенном месте. Код такой:
Соответственно все диаграммы строятся на одном и том же месте, друг за другом.
которое не работает
Увы, и этот метод снова не работает. |
|||||
так как здесь идет указание на диаграмму, которая уже была создана. |
Андрей VG  Пользователь Сообщений: 11878 Excel 2016, 365 |
#2 16.10.2014 07:02:37 Доброе время суток
на следующий код
Успехов. |
||||
|
Alejandro67 Пользователь Сообщений: 19 |
#3 16.10.2014 09:49:54 Спасибо, Работает, только надо потом вернуть выделение
данный код соответственно будет размещать график по координатам листа. Если надо разместить график по координатам относительно видимой части листа то нужен такой код:
|
||||||
|
Максим Зеленский  Пользователь Сообщений: 4658 Microsoft MVP 2018-2022 |
#4 16.10.2014 13:07:33
любопытный эффект. Не думал, что Increment именно так работает — отсылок на видимый диапазон в справке нет F1 творит чудеса |
||
|
apelmon  Пользователь Сообщений: 1 |
Здравствуйте. Помогите пожалуйста. Не могу запустить записаный макрос. Ошибку в коде выдает на строке ActiveSheet.Shapes.AddChart.Select. |
|
Dima S  Пользователь Сообщений: 2063 |
#6 19.03.2017 14:18:55
потому что это не так)
у меня запускается) вашего не вижу. |
||||
Since you want the chart to be on a new sheet,
you have to change the macro’s «Sheet1» to new worksheet’s name,
The following macro should work for you, I have named the new worksheet as newWs
And fyi, your macro’s error message I believe is due to trying to creating 2 pivot table of the same name on «Sheet1» is not allowed.
He also like to know what to create pivotTable base on Selected Area, so I have done modification to the code.
Edited: I Assume you select 2 columns each time
Sub chart1()
'
' chart1 Macro
'
'
Dim selectedSheetName As String
Dim newWs As Worksheet
Dim rangeName As String
Dim header1 As String
Dim header2 As String
header1 = ActiveSheet.Cells(1, Selection.Column).Value
header2 = ActiveSheet.Cells(1, Selection.Column + 1).Value
selectedSheetName = ActiveSheet.Name
rangeName = Selection.Address
Set newWs = Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
selectedSheetName & "!" & rangeName, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=newWs.Name & "!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
newWs.Activate
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header1)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header2)
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(header2), "Count of answer1", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header2)
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields(header2).Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header2)
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(newWs.Name & "!$A$3:$D$6")
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(newWs.Name & "!$A$3:$D$6")
ActiveChart.ChartType = xlColumnClustered
End Sub
-
06-06-2013, 04:29 PM
#1
Forum Contributor
ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
Hello!
I have some code below that used to work and now all of a sudden it doesn’t.
The code is failing atActiveSheet.Shapes.AddChart.Select
I have never had an issue with the code not running through the entire procedure. Is there a work around for where the code is failing?Thank you!
-
06-06-2013, 04:32 PM
#2
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
I don’t know why it fails but selecting is not required
Josie
if at first you don’t succeed try doing it the way your wife told you to
-
06-06-2013, 04:42 PM
#3
Forum Contributor
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
Hey JP,
I tried your code, and it still fails at the first line
ActiveSheet.Shapes.AddChart.Chart
Any more thoughts?
-
06-06-2013, 04:43 PM
#4
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
did you protect the sheet? or share the workbook?
-
06-07-2013, 09:08 AM
#5
Forum Contributor
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
Hey JP,
Sheet is not protected nor has the workbook been shared.
-
06-07-2013, 09:22 AM
#6
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
do you have more than one sheet selected?
-
06-07-2013, 10:02 AM
#7
Forum Contributor
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
No, only one sheet is activated…
-
06-07-2013, 10:36 AM
#8
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
-
06-07-2013, 11:27 AM
#9
Forum Contributor
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
Including this clause works:
-
06-07-2013, 11:30 AM
#10
Re: ActiveSheet.Shapes.AddChart.Select Run-Time Error ‘1004’
that’s not the same thing-it gets a reference to an existing chart rather than creating a new one. also the
is totally unnecessary (you ought never to need that!)
-
#1
I have the following code that I have used for 4 months now and all of a sudden it decides that it is not going to work.
Sub BkPremCharts()
Columns(«F:F»).ColumnWidth = 1.8
Set ws_data = Sheets(wsBPM)
Application.DisplayAlerts = False
Sheets(wsBPM).Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range(«B8:D20»)
ActiveChart.ApplyLayout (3)
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = «Total»
ActiveChart.ApplyLayout (1)
With ws_data.Range(«g7»)
L = .Left
T = .Top
W = .Width
H = .Height
End With
With ActiveChart.Parent
.Left = L
.Top = T
.Width = W * 6
.Height = H * 10
End With
ActiveChart.Parent.Name = «BPM1»
Set ws_data = Sheets(wsBPM)
Application.DisplayAlerts = False
Sheets(wsBPM).Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range(«B27:D39»)
ActiveChart.ApplyLayout (3)
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = «Underwriter»
ActiveChart.ApplyLayout (1)
With ws_data.Range(«g26»)
L = .Left
T = .Top
W = .Width
H = .Height
End With
With ActiveChart.Parent
.Left = L
.Top = T
.Width = W * 6
.Height = H * 12.5
End With
ActiveChart.Parent.Name = «BPM2»
Range(«B25»).Select
End Sub
I am getting the following error «method shapes of object worksheet failed» it is occuring on the ActiveSheet.Shapes.AddChart.Select line in both sections.
Any ideas why this would all of a sudden show up??
Thank you
Dave
What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
-
#2
Try like this
Code:
Dim ch As ChartObject
Set ch = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
ch.Chart.ChartType = xlColumnClustered
ch.Chart.SetSourceData Source:=Range("B8:D20")
-
#3
VoG —
Why would it all of a sudden start messing up? I guess that is the greater question that I would like answered.
Dave
-
#4
VoG —
I put your code in and got a new error.
Automation Error — The object invoked has disconnected from its clients.
Dave
-
#6
VoG —
My boss and I figured out what was going on. Somehow the sheet got corrupted or something like that. Ended up deleting and recreating with the same name and it worked. So it was actually nothing with the code. Thanks for all the help. Anyone else who reads this if you have ever heard of or encountered an issue like this could you explain.
Dave