Copy n Templates to a single new worksheet (with charts)

[tyris]

Hi,

I have 2 templates. All templates contains charts, with a macro that takes data from a 2nd sheet and defines them as datasource for each chart.

I will make a loop, that makes 1 instance of template 1 and n instances of the template 2. And call the macro.

I want to copy (append) to a new file, in the same worksheet, result of template 1, then just after result of template 2, n times, one after the other.

I'd like to know if it's doable in VBA, and what method should i use (i'm not very familiar with VBA, i'm a .Net programer)

Thanks

regards,
Elise
(author of french .NET tutorial like

http://dotnet-tech.com)

 

[tyris]

I based my code on :

Sub TestCopy()
    For i = 2 To Worksheets.Count
      Worksheets(i).Range("A1").CurrentRegion.Copy Destination:=ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Next i
End Sub

but i have one problem :

the charts are copied, but they keep the datasource linked to the same area.

this is a problem, because, i :
- open a template from my .net program
- fill the data on a sheet
- refresh the charts with this data
- copy the content of the chart sheet to an other sheet
- close the template
- open a new template...
- then the result is a sheet that contains n copy of templates. so i wont keep the data sheet.

Am i forced to make a "picture" copy of the charts ? this gonna make a very very huge file !!



regards,
Elise
(author of french .NET tutorial like

http://dotnet-tech.com)

 

[PHV]

You may perhaps try the PasteSpecial method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tyris]

i tryed something like this...

Sub TestCopy()

    For i = 2 To Worksheets.Count
      Worksheets(i).Range("A1").CurrentRegion.Copy
      ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
    Next i
End Sub

but with this code, this doesnt even copies the charts any more !

regards,
Elise
(author of french .NET tutorial like

http://dotnet-tech.com)