How to resize a bar chart using VBA

[LeanidTerbant]

I have created a macro for copying a bar chart from one sheet to another. I need to be able to resize the chart on the second sheet so that takes up more cells than it did on the other sheet. Is this possible using VBA?

Thanks

 

[pgc01]

Hi

This is an example.

You have a chart called "Chart 1" in Sheet1. You want to copy it to Sheet2 and there you want to resize it so that it overlaps the range C3:M20.

Try:

Sub CopyChart()
Dim wsSrc As Worksheet, wsDest As Worksheet
Dim chtO As ChartObject
Dim r As Range
 
Set wsSrc = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")
Set r = wsDest.Range("C3:M20")
 
wsSrc.ChartObjects("Chart 1").Copy
wsDest.Paste
Set chtO = wsDest.ChartObjects(wsDest.ChartObjects.Count)
 
With chtO
    .Left = r.Left
    .Top = r.Top
    .Width = r.Width
    .Height = r.Height
End With
 
End Sub

 

[LeanidTerbant]

Brilliant, thanks

 

[flaco]

Hello everyone.
I have a few excel files, they are all the same but with different data in them, which generates different graphs in different sheets. What I want to do, is in a new Excel file, which I already have, have a macro that will go through every file in that folder and copy all Chart1 of each workbook and paste and resize them in sheet1 of the workbook with the macro, then copy all chart2 of each workbook and copy and paste/resize them in sheet2 of the file, and so on.

Is there a way I can do this with VBA? I really need some help! Thank you sooo much.