Copy multiple charts to multiple worksheetsSolved/Closed

Question

Hi

I have a challenge for which I cannot correctly conceptualize a workable solution so I am hoping someone here can share their thoughts on it.

I have a workbook (Charts) with charts (Chart 1 - Chart 197) based on data from different countries in 1 worksheet. I also have another workbook (Country) with other country data - Each country has its own worksheet (197 worksheets). The only thing in common between the two workbooks is the sequencing - Chart 1 relates to the 1st country's worksheet, chart 2 relates to 2nd country's worksheet etc.

I would like to have the "Charts" charts showing up in the "Country" workbook at the bottom of the current information - e.g. respective charts should appear on row 20 of the respective country's worksheet.

I would like to copy all 197 charts at the same time rather than doing them one at a time - Is this possible? 

Also, is it possible to reference a chart name using a variable parameter? E.g. ActiveSheet.ChartObjects("Chart " & ChartCounter).Copy

In other words, first time thru a loop it will reference "Chart 1", next time it will reference "Chart 2" etc. 

Thanks for any suggestions.

ac3mark · Answer

If I may,

Your scope is quite a challenge!  Is this a school project?

To address the question:
"I would like to copy all 197 charts at the same time rather than doing them one at a time - Is this possible? "

--No. it will happen serially!

ibtrini · Answer

No, it is not a school project. VBA, Macros, and Excel is not my strength however being in IT I was asked for some input prior (create a workbook with worksheets for each country based on a template and another a data workbook). They were so pleased with the end result they decided to ask if it was possible to included charts (from another workbook) which will enhance their prior project. I do not mind helping anyone as I always end up gaining more knowledge in the long run.

I did not understand what you meant by "No, it will happen serially." Is it possible to have this automated so it can run thru a loop to accomplish this step or can it only be done manually one at a time?

Thanks for replying.

ibtrini · Answer

Thanks for taking the time to think about this. 

"Country" workbook :
Worksheets (197) named for each country - Angola, Algeria... Zimbabwe. Each country is on its own worksheet.

Each worksheet has  relevant data (17 rows) pertaining to that country (Population, drinking patterns - % males, % females,  consumption by type - beer, wines, spirits... etc)

"Charts" workbook has 1 worksheet:
Column A - Country names (Angola, Algeria... Zimbabwe) on 197 rows with charts. Chart 1 refers to Angola, Chart 2 refers to Algeria... Chart 197 refers to Zimbabwe.

Desired outcome:
A macro to update the "Country" worksheets to include respective charts from the "Charts" workbook. In other words,  the "Angola" worksheet should contain the "Angola" chart (Chart 1) on row 18, "Algeria" worksheet should contain the "Algeria" chart (Chart 2) on row 18 etc...

Thanks again.

ibtrini · Answer

Well I finally figured out a solution to my problem so I will post it here in case it can be useful to anyone else. This exercise was part of a prior routine (https://ccm.net/forum/affich-716133-macro-to-create-tabs-and-populate-from-other-worksheets#p716553) so I will give a recap and post the solution below. 

1st workbook (SampleFile) contained 2 worksheets (Data and Template)
"Data" - Column A - 197 country names, Columns B-BU - 72 different pieces of information on each country
"Template" - Column A - Field names referenced from above. Column B to receive populated data with a chart below it.

2nd workbook (ChartFile) contained 1 worksheet (AFR A)
Charts for each country - 197 charts

Desired result was to create a macro to add worksheets for each country, rename the worksheet to the appropriate country name, populate that worksheet with a template then populate it with the relevant data from "SampleFile" and the respective chart from "ChartFile". If the macro needs to be rerun the country tabs will need to be deleted first.

The only way I could get the chart to paste accurately into the "SampleFile" was by using "ActiveSheet.Pictures.Paste.Select". What was just as confusing was the pasted object was not a picture and it even had the server location of the file/series being used. I am sure there are some redundant lines of coding in here but for now it does what I needed it to do.

Sub CreateSheetsFromAList()

' Create an array
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Data").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

' Loop through country range - Get Country name
For Each MyCell In MyRange

' Worksheet creation - Add new, blank worksheet
Sheets.Add After:=Sheets(Sheets.Count)

' Worksheet renaming - Rename to appropriate country
Sheets(Sheets.Count).Name = MyCell.Value

' Select Range from "Template" to use with newly created worksheet
Sheets("Template").Select
Range("A1:M32").Select
Selection.Copy
Sheets(Sheets.Count).Select
Range("A1").Select

' The copy sequence here made a huge difference
' Copy layout from the source template (formatting cells)
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

' Copy static data from the source template
ActiveSheet.Paste

' Assign Country value to cell to be used in the "Match" function
Range("B1:B1") = MyCell.Value

' Populate newly created worksheet using string concatenation in the "Index" function
Range("B3:B3") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A3,Data!B1:J1,0))"
Range("B4:B4") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A4,Data!B1:J1,0))"
Range("B8:B8") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A8,Data!B1:J1,0))"
Range("B9:B9") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A9,Data!B1:J1,0))"
Range("B12:B12") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A12,Data!B1:J1,0))"
Range("B13:B13") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A13,Data!B1:J1,0))"
Range("B14:B14") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A14,Data!B1:J1,0))"
Range("B16:B16") = "=INDEX(Data!B2:J5,MATCH(" & MyCell.Value & "!B1,Data!A2:A5,0),MATCH(" & MyCell.Value & "!A16,Data!B1:J1,0))"

' Copy charts from the "Charts" workbook and paste as a picture in the "SampleFile" 'workbook under the appropriate country worksheet.

Sheets(Sheets.Count).Select
ActiveSheet.ChartObjects("Chart 1").Activate
Windows("ChartsFile.xlsm").Activate

' Select appropriate worksheet in "ChartsFile"
Sheets("AFR A").Select
ActiveSheet.ChartObjects(MyCell.Value).Activate
ActiveChart.ChartArea.Copy
Windows("SampleFile.xlsm").Activate
ActiveSheet.Pictures.Paste.Select

' Return to beginning of loop

Next MyCell

Application.CutCopyMode = False

End Sub

Copy multiple charts to multiple worksheets

4 responses

Similar discussions

Subscribe To Our Newsletter!