Good Morning All,
What I am trying to accomplish is I have a cell range that I would like to use as sheet names. I have an Averages worksheet at the end of the workbook and have roughly 50 sheets in front. I'd like to get the averages for the last 6 sheets which is before the Averages sheet. The formula below has been shortened for testing.The Msgbox populates the correct sheet name based on the variable but I can't get the formula to work. I get an error "Object doesn't support this property or method."
Thanks for taking the time to look this over.
What I am trying to accomplish is I have a cell range that I would like to use as sheet names. I have an Averages worksheet at the end of the workbook and have roughly 50 sheets in front. I'd like to get the averages for the last 6 sheets which is before the Averages sheet. The formula below has been shortened for testing.The Msgbox populates the correct sheet name based on the variable but I can't get the formula to work. I get an error "Object doesn't support this property or method."
Thanks for taking the time to look this over.
Code:
Private Sub btnGetAverages_Click()
Dim rngM3 As String, shtM3 As Worksheet
rngM3 = Sheets("Averages").Range("M3").Value
Set shtM3 = Sheets(rngM3)
Dim rngM4 As String, shtM4 As Worksheet
rngM4 = Sheets("Averages").Range("M4").Value
Set shtM4 = Sheets(rngM4)
With Worksheets("Averages")
'MsgBox shtM3.Name
'Exit Sub
.Range.Formula = _
"=AVERAGE(" & shtM3 & "!D3," & shtM4 & "!D3)"
'Debug.Print shtM3
End With
End Sub