Dear all,

I have encountered a problem. Suppose I have a Excel macro which opens a
data workbook, say "myExcel.xls". When the program runs, within a loops it
copies different range of data from myExcel.xls and generates new excel
workbooks, then copies the selected data to the new workbook. The name of the
newly created workbooks starts from Sheet1, the next one is Sheet2, and so
on.....

However, the program needs to switch between "myExcel.xls" and the new
workbooks during the loop. Here comes the problem, e.g. when the program
activates the new workbook "Sheet1", it does not activate workbook "Sheet1'
actually, it stays at worksheet "Sheet1" of "myExcel.xls" instead. There is a
name conflict of "Sheet1", "Sheet2" and "Sheet3" (myExcel.xls has 3 sheets).
Therefore, how should I avoid this problem??

Can anyone advise?? Thanks a million....

Ivan

Hello Ivan,

All you need to do is fully qualify the function. All sheets and range
in the workbook that has the macro should be prefaced with...

ThisWorkbook.Worksheets("Sheet")

Worksheet references can be simplifieed like this...

Dim Wks1 As Worksheet
Set Wks1 = ThisWorkbook.Worksheets("Sheet1")

Those in the new workbook can be refered to using an object referenc
we will call NewWkb.

'Declare the object variable
Dim NewWkb As Workbook

'Execute this line after the new Workbook has been created
Set NewWkb = ActiveWorkbook

You can set an object variable to refence a worksheet in the ne
workbook also...

Dim NewWks1 As Worksheet
Set NewWks1 = NewWkb.Worksheets("Sheet1")

Prefix your ranges with the worksheet variables to access the correc
range. This also makes your code easier to follow. This example set A
in the macro workbook equal to the new workbook's range A1 value.

X = NewWks1.Range("A1").Value
Wks1.Range("A1").Value = X

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=49632

Dear Leith,

Thanks a lot! It helps.

Ivan

"Leith Ross" wrote:


Hello Ivan,

All you need to do is fully qualify the function. All sheets and ranges
in the workbook that has the macro should be prefaced with...

ThisWorkbook.Worksheets("Sheet")

Worksheet references can be simplifieed like this...

Dim Wks1 As Worksheet
Set Wks1 = ThisWorkbook.Worksheets("Sheet1")

Those in the new workbook can be refered to using an object reference
we will call NewWkb.

'Declare the object variable
Dim NewWkb As Workbook

'Execute this line after the new Workbook has been created
Set NewWkb = ActiveWorkbook

You can set an object variable to refence a worksheet in the new
workbook also...

Dim NewWks1 As Worksheet
Set NewWks1 = NewWkb.Worksheets("Sheet1")

Prefix your ranges with the worksheet variables to access the correct
range. This also makes your code easier to follow. This example set A1
in the macro workbook equal to the new workbook's range A1 value.

X = NewWks1.Range("A1").Value
Wks1.Range("A1").Value = X

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=496321