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]

Something like this:

Sub TotalPaidClaims()
   Dim cRange            As Range
   Dim pClaims           As Long

   With Workbooks("April2011").Sheets("01Apr2011")
      Set cRange = .Range("EE2:EE" & .Cells(.Rows.Count, 1).End(xlUp).Row)
      pClaims = Application.WorksheetFunction.CountIf(cRange, "Paid")
      .Range("EH2").Value = pClaims
      .Range("EH1").Value = "Paid Claims"
      .Range("EG1").Value = "Select Type"
      .Range("EK1").Value = "Risk Based Selected"
      .Range("EJ1").Value = "Risk Based Minimum"
      .Range("EI1").Value = "SVS"
      .Range("EL1").Value = "Random Selected"
   End With
End Sub

 

[Gos-C]

Thanks for your kind response, rorya.

It didn't work. I got:

Run-time error '9':
Subscript out of range

 

[RoryA]

Sorry forgot the file extension:

With Workbooks("April2011.xls").Sheets("01Apr2011")

adjust as required if .xlsx or other.

 

[Gos-C]

Yes! It worked. Thanks a million, rorya.

Keep on "Excel-ing."

Best regards,
Gos-C

 

[Gos-C]

Hi rorya,

I have another subroutine for a userform:

Sub cmdOnSite_Click()
Dim rskMin As Integer
With Workbooks(Sheet1.fName & ".csv").Sheets(Sheet1.sName)
If .Range("EH2") <= 49 Then
    rskMin = .Range("EH2")
ElseIf .Range("EH2") <= 299 Then
        rskMin = 50
    ElseIf .Range("EH2") <= 499 Then
            rskMin = 100
        ElseIf .Range("EH2") <= 699 Then
                rskMin = 125
            ElseIf .Range("EH2") <= 999 Then
                    rskMin = 150
                ElseIf .Range("EH2") <= 4999 Then
                        rskMin = 175
                    ElseIf .Range("EH2") >= 5000 Then
                            rskMin = 225
End If

.Range("EJ2") = rskMin
Unload frmTypeOfAudit
End With
End Sub

I have declared the following variable in Sheet1:

Option Explicit
Public fName As String, sName As String, prov As String, pClaims As Long, rskMin As Integer, cRsk As Integer, sRange As Range, cRange As Range

However, I am getting the same error when I run the userform code:

Run-time error '9':
Subscript out of range

Can help me with this too, please.

Thank you,
Gos-C

 

[RoryA]

It would appear that either the workbook or sheet name is wrong. Can't be more specific than that, I'm afraid.

 

[Gos-C]

OK, I moved the code (with the goblal declaration of the variables) from the sheet module to a standard module. Now, the userform code is working.

However, the following "Selection" code (which is in the Sheet1 module of the TestMacro workbook is not working. I need to make the selections on the 01Apr2011 sheet in the April 2011 workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nResult As Long
'With Workbooks(fName & ".csv").Sheets(sName)
Set sRange = Range("EG2:EG" & Cells(Rows.Count, 1).End(xlUp).Row)
If Target.Cells.Count > 1 _
Or Intersect(Target, Range("EG2:EG" & Cells(Rows.Count, 1).End(xlUp).Row)) Is Nothing Then Exit Sub
        If UCase(Cells(Target.Row, "EE")) <> "PAID" Then Exit Sub
        
                If Target = vbNullString Then
                    Target = "RK"
                Else
                    Target = vbNullString
                End If
Range("EK2") = Application.WorksheetFunction.CountIf(sRange, "RK")
If Range("EK2") >= Range("EJ2") Then

nResult = MsgBox( _
Prompt:="You have already selected the minimum Risk Based Claims.  Do you want you want to select the Random Claims now?", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
Else
Call Random_Claims_Selection
Call CopySelectedClaims
MsgBox Application.WorksheetFunction.Sum(Range("EK2"), Range("EL2")) & " (" & Range("EK2") & " Risk Based and " & Range("EL2") & " Random) Claims have been selected for auditing."
End If
End If
Application.ScreenUpdating = True
End Sub

I tried "With Workbook . . . " pattern from rorya's posts but that didn't work.

Can someone help me again, please.

Thank you,
Gos-C

 

[Gos-C]

Hi all,

Maybe someone would be able to help me with this question:

In which module should I place a Worksheet_SelectionChange code so that it executes in another workbook, and how should it be coded?

I stuck, and critically falling behind in accomplishing the task.

I would appreciate all suggestion - maybe, one would work.

Thank you,
Gos-C

 

[Gos-C]

I have double-posted this question as I am in dire need of a solution. Here is the link to my other post: http://www.excelforum.com/excel-pro...tion-in-a-different-workbook.html#post2576963

Regards,
Gos-C