[RESOLVED] How do I reference changes to be made in a copied workbook

[yorch]

I coud not find the cause of an Automation Error. The only thing I know is that, if the workbook that I copy, does not have any charts (objects) within, it does not create the error. So, I create the charts after the workbook copy, but I want also modify font and borders to the new workbook, the problem is that the modifications were done in the original workbook. Take a look:

Worksheets.Copy

With Sheets("DTC Search Result").ChartObjects.Add(29, 1120, iDTCX * 66, 250).Chart

Here I have instructions of the new chart. This chart was correctly created in the new cloned workbook!.

End With
sheDTC.Range(sheDTC.Cells(83, 2), sheDTC.Cells(84, iDTCX - 1)).Borders.LineStyle = xlContinuous
sheDTC.Range(sheDTC.Cells(83, 3), sheDTC.Cells(83, iDTCX - 1)).Font.Bold = True
sheDTC.Range(sheDTC.Cells(83, 2), sheDTC.Cells(83, iDTCX - 1)).Font.ColorIndex = 2
sheDTC.Range(sheDTC.Cells(83, 2), sheDTC.Cells(83, iDTCX - 1)).Interior.ColorIndex = 49

This last modifications was not maden in the new workbook (what I want), instead, they were done in the old-original workbook.

How do I reference to the new workbook?

Thanks!.

[Webtest]

If you are going to swap back and forth between WorkBooks or sheets, you should create a "Handle" for each. Here are some hints:

Option Explicit
'
Sub Macro1()
'You will need some variables:
Dim srcBook as Workbook
Dim dstBook as Workbook
Dim srcSheet as Worksheet
Dim dstSheet as Worksheet

Set srcBook = ActiveWorkbook
'"srcBook" is now the handle for the Source Workbook
Set srcSheet = ActiveSheet
'"srcSheet" is now the handle for the current sheet IN THE SOURCE WORKBOOK!
'Any reference to "srcSheet" automatically refers to the Source Workbook!

'
'Now create your copy of the Source Workbook
'

'  Immediately after you create the copy, do ...
Set dstBook = ActiveWorkbook
'"dstBook is now the handle for referencing the Destination Workbook
Set dstSheet = ActiveSheet
'"dstSheet" is now the handle for the current sheet IN THE DESTINATION WORKBOOK!
'Any reference to "dstSheet" automatically refers to the Destination Workbook!

I hope this gives you some idea about referencing Books and Sheets programmatically.

[yorch]

Actually I tried modifications of what you gave me and I could do it shorter.

Instead of:
sheDTC.Range(sheDTC.Cells(83, 2), sheDTC.Cells(84, iDTCX -1)).Borders.LineStyle = xlContinuous

I wrote:
ActiveWorkbook.Sheets("DTC Search Result").Range(Sheets("DTC Search Result").Cells(83, 2), Sheets("DTC Search Result").Cells(84, iDTCX - 1)).Borders.LineStyle = xlContinuous

And it worked.

Thanks for the command!.
Yorch.