VBA Code to Activate and Write to Sheet in Another Workbook

[Gos-C]

Hi all,

I have the following code in a common workbook, TestMacro, (i.e., one used by several users) which I need to use in different workbooks:

Sub TotalPaidClaims()
Dim cRange As Range, pClaims As Long
    Set cRange = Range("EE2:EE" & Cells(Rows.Count, 1).End(xlUp).Row)
    pClaims = Application.WorksheetFunction.CountIf(cRange, "Paid")
    Range("EH2") = pClaims
    Range("EH1") = "Paid Claims"
    Range("EG1") = "Select Type"
    Range("EK1") = "Risk Based Selected"
    Range("EJ1") = "Risk Based Minimum"
    Range("EI1") = "SVS"
    Range("EL1") = "Random Selected"
End Sub

The above ranges are in worksheet 01Apr2011, which is in a workbook (named April2011) - and is opened.

However, when I run the macro, it is reading and writing to Sheet1 of the TestMacro workbook instead of 01Apr2011 of the April2011 workbook.

Can any help me with getting the 01Apr2011 sheet to be the active sheet, please.

Thank you,
Gos-C

 

[RoryA]

What exactly do you mean by "so that it executes in another workbook"?

If you use the SelectionChange event, the code has to be in the code module of the sheet that you want to trigger the code, but the data itself can be anywhere as long as you specify which workbook and worksheet you are trying to manipulate in the code.