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.
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?
"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.
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...
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.
' 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 renaming - Rename to appropriate country
Sheets(Sheets.Count).Name = MyCell.Value
' Select Range from "Template" to use with newly created worksheet
' The copy sequence here made a huge difference
' Copy layout from the source template (formatting cells)
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
' Copy static data from the source template
' Assign Country value to cell to be used in the "Match" function
Range("B1:B1") = MyCell.Value