I have 10 Workbooks that all have identical Modules and VB code functions.

I want to centralize that code in one place so that fixes don't have to be
made 10 times. I created an Add-in with a Module containing all of the
common code. The problem now is that this common code refences the worksheet
cells (which don't exist in the Addin) as ThisWorkbook. I want it to
reference what ever workbook was opened. How do I do this?

So for example the code in the Addin Module1 has:

Set wsh = ThisWorkbook.Sheets("DW")
ThisWorkbook.Sheets("DW").Activate
'Assumes data starts in column D
Set rRng = wsh.Range("D11", "AY11")

On Error GoTo Err_Handle

sContractCode = Worksheets("DW").Range("D5").Value
dStartDate = Worksheets("DW").Range("D7").Value

etc.,etc...

ThisWorkbook refers to the workbook that contains the code, your addin

You will need something that's common to all workbooks that's applicable to
the code in your addin. Let's assume it's any workbook that contains a sheet
named "DW" and the code will only be called in the ActiveWorkbook


On Error Resume Next
Set wsh = Nothing ' if there's any possibility wsh is already ref'd
Set wsh = ActiveWorkbook.WorkSheets("DW") ' assuming it's a worksheet
On Error GoTo Err_Handle

If wsh is nothing then
' msgbox "Sheet DW does not exist in activeworkbook
Exit Sub
End if

wsh. Activate ' only to need to present to user

' etc

Regards,
Peter T


"chemicals" wrote in message
...
I have 10 Workbooks that all have identical Modules and VB code functions.

I want to centralize that code in one place so that fixes don't have to be
made 10 times. I created an Add-in with a Module containing all of the
common code. The problem now is that this common code refences the
worksheet
cells (which don't exist in the Addin) as ThisWorkbook. I want it to
reference what ever workbook was opened. How do I do this?

So for example the code in the Addin Module1 has:

Set wsh = ThisWorkbook.Sheets("DW")
ThisWorkbook.Sheets("DW").Activate
'Assumes data starts in column D
Set rRng = wsh.Range("D11", "AY11")

On Error GoTo Err_Handle

sContractCode = Worksheets("DW").Range("D5").Value
dStartDate = Worksheets("DW").Range("D7").Value

etc.,etc...

Thanks. I knew that "ThisWorkbook" was referring to the Add-in wb but I also
assumed that the ActiveWorkbook would be the Add-in as well.... I changed
ThisWorkbook to ActiveWorkbook everywhere in the Add-in module and now it
works great...



"Peter T" wrote:

ThisWorkbook refers to the workbook that contains the code, your addin

You will need something that's common to all workbooks that's applicable to
the code in your addin. Let's assume it's any workbook that contains a sheet
named "DW" and the code will only be called in the ActiveWorkbook


On Error Resume Next
Set wsh = Nothing ' if there's any possibility wsh is already ref'd
Set wsh = ActiveWorkbook.WorkSheets("DW") ' assuming it's a worksheet
On Error GoTo Err_Handle

If wsh is nothing then
' msgbox "Sheet DW does not exist in activeworkbook
Exit Sub
End if

wsh. Activate ' only to need to present to user

' etc

Regards,
Peter T


"chemicals" wrote in message
...
I have 10 Workbooks that all have identical Modules and VB code functions.

I want to centralize that code in one place so that fixes don't have to be
made 10 times. I created an Add-in with a Module containing all of the
common code. The problem now is that this common code refences the
worksheet
cells (which don't exist in the Addin) as ThisWorkbook. I want it to
reference what ever workbook was opened. How do I do this?

So for example the code in the Addin Module1 has:

Set wsh = ThisWorkbook.Sheets("DW")
ThisWorkbook.Sheets("DW").Activate
'Assumes data starts in column D
Set rRng = wsh.Range("D11", "AY11")

On Error GoTo Err_Handle

sContractCode = Worksheets("DW").Range("D5").Value
dStartDate = Worksheets("DW").Range("D7").Value

etc.,etc...