moved code to new book - some doesn't work now

[lallen]

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?

 

[joel]

When you have a statement like this

With Worksheets("Sheet1")

It is using the activeworkbook. Since you have two workbooks that are
opened you are probably accessing the wrong workbook. if you are
refereing to the workbook where the macro is located use Thisworkbook

With Thisworkbook.Worksheets("Sheet1")


You may also want to pass as a parameter to the macro the workbook name

Application.Run "'Book2'!Macro1" "Book1"

 

[OssieMac]

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

 

[Dave Peterson]

Except that doesn't explain how the second line worked.

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

I don't have a good guess why one line would work, but the other not.

I would guess that the code posted in the message wasn't the same as the real
code.

Maybe that difference is enough to hide the real solution to the problem.

 

[lallen]

That did it. Thank you.

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