The code works fine when I run it manually, but when I call it in the calculate change event I get Run-time error '1004': Method 'Range' of object 'Worksheet failed. I simply want to copy the data that is specified in wksD4D to the specified cells in wksGenerator when Index_Count value changes (Index_Count is just a cell in wksGenerator with a formula).
The set of code is below:
Sub Initiate_Generator()
Dim wksGenerator As Worksheet
Dim wksD4D As Worksheet
Dim callCount As Range
Dim fullName As Range
Dim address As Range
Dim rngName As Range
Dim rngAddress As Range
Set wksGenerator = Worksheets("Generator")
Set fullName = wksGenerator.Range("A3")
Set address = wksGenerator.Range("B3")
Set callCount = wksGenerator.Range("Index_Count")
Set wksD4D = Worksheets("D4D")
Set rngName = wksD4D.Range("A" & callCount)
Set rngAddress = wksD4D.Range("B" & callCount)
'Fill values from D4D to Generator
fullName.Value = rngName
address.Value = rngAddress
End Sub
'This sheet1 also known as wksGenerator
Private Sub Worksheet_Calculate()
Static previousIndex
Static previousNum
If Range("Index_Count").Value <> previousIndex Then
Call Initiate_Generator
previousIndex = Range("Index_Count").Value
End If
' If Range("Dial_Num").Value <> previousNum Then
' Call Dialing_Number
' previousNum = Range("Dial_Num").Value
' End If
End Sub
