Adding SUM function to VBA Macro including Sheet Name

[3LydonsOnAShirt]

Hi there,

In a workbook that has multiple worksheets, I have an existing macro that selects data from specific cells in a specific worksheet. I'd like to add to this, and have the macro SUM a selection of cells within the specified worksheet.

I have tried adding:
Range("K" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Application.WorksheetFunction.Sum(Range("W50:AS50"))

However, it didn't return the results I expected, which I think might be because it is not picking up the Worksheet, and selecting the range from the first sheet in the workbook.

Any ideas how I can adjust this??? My macro (excluding my SUM line) is below:

Sub Extract_Solution_Consultants()
'***********************************************************************************************************************************
' PURPOSE:
' INPUTS:
' OUTPUTS:
'
'***********************************************************************************************************************************


'HOUSEKEEPING ************************************************************************************************************************
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'************************************************************************************************************************************


'MAPPING ***************************************************************************************************************************
'Read & write paths
Const importPath = "I:\Everyone\Enterprise CSO\Commissions\Statements Macro\Annie's Statements\"
'*************************************************************************************************************************************


'EXCTRACT DATA FROM PACK(S) ***********************************************************************************************************************
Dim tempBookName As String
Dim tempCount As Long

Dim tempReportRecordCount As Long
Dim tempPackRecordCount As Long
Dim tempHeader As String
Dim refColumn As String


tempCount = 2
tempBookName = Dir(importPath)

Do While tempBookName <> ""

'Open book
Set bkTempPack = Workbooks.Open(Filename:=importPath & tempBookName, UpdateLinks:=0)

'Grab data
ThisWorkbook.Sheets("Extraction - SME SCs").Activate
Range("A" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("C3").Value
Range("B" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("C4").Value
Range("C" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("C5").Value
Range("D" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("C6").Value
Range("E" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("B17").Value
Range("F" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("E15").Value
Range("G" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("E16").Value
Range("H" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("G16").Value
Range("I" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("L16").Value
Range("J" & tempCount).Value = bkTempPack.Sheets("1.Current_Month_Payment_YTDa").Range("O47").Value
Range("K" & tempCount).Value = tempBookName

'Close pack
bkTempPack.Close SaveChanges:=False

'Move to next file
tempBookName = Dir

tempCount = tempCount + 1
Loop
'************************************************************************************************************************************


'HOUSEKEEPING **************************************************************************************************************************
MsgBox ("Done!")
'****************************************************************************************************************************************

End Sub
​

 

[JoeMo]

try:

Range("K" & tempCount).Value = WorksheetFunction.Sum(Sheets("1.Current_Month_Payment_YTDa").Range("W50:AS50"))

 

[3LydonsOnAShirt]

Hi JoeMo,

I am still getting an error this code?

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