View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
lallen lallen is offline
external usenet poster
 
Posts: 17
Default moved code to new book - some doesn't work now

That did it. Thank you.

"OssieMac" wrote:

When you place code in another workbook then you need to specify which
workbook the code is to be applied to whenever it is run. If this is not done
then the code will most likely operate on whatever workbook is the active
workbook at the time. I am surprised that you say one line of the code works
and the other does not however, I am attributing that to maybe an error when
attempting to post a simplified version of your code.

One way of achieving the above is to pass the workbook to the code as an
argument at the time it is called and pick up the workbook as a parameter in
the called sub. Then use the parameter in the code.

The calling code:
Application.Run "'Book2'!Macro1", ThisWorkbook


The called macro. (Note the parameter wb in the Sub line and the use of
wb.Worksheets("Sheet1") where the variable wb refers to the calling workbook.)

Sub Macro1(wb As Workbook)

With wb.Worksheets("Sheet1")
.Columns("A:I").ClearContents
.Cells(10, 3) = "Title1"
End With

End Sub


--
Regards,

OssieMac


"lallen" wrote:

I recently moved all the code from one workbook to another so that it could
be shared amont multiple books, by making the following change in the
worksheet_activate sub in Sheet1:

from Call Macro1
to Application.Run "'Book2'!Macro1"

Now, some of the code works, and some doesn't. For example:

With Worksheets("Sheet1")
.Columns("A:I").ClearContents ' does nothing
.Cells(10,3) = "Title1" ' works
End With

What am I doinf wrong?