K
Karen53
Hi,
For some reason the "Advisor" sections run fine. As soon as the ws name
changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld
and wbkOld are set. Debug gives me the correct names. Is there something
about global variables I've missed?
Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell)
Dim Lusedrow As Long
Dim NLusedrow As Long
'Advisor
If wsOld.Name = "Advisor" And DataType = "ThisData" Then
Debug.Print "Starting Advisor ThisData " & DataType
'get the last used row
Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _
End(xlUp).Row
Debug.Print "Advisor ThisData Lusedrow " & Lusedrow
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow)
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2)
Lusedrow = 0
End If
'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _
& DataType
If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then
Debug.Print "Starting Advisor ThisVolume " & DataType
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C11")
'remove the lapsed month
wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp
'add the new month data
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15")
Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15")
End If
Debug.Print "wbkOld.name " & wbkOld.Name
'Annuity
If wsOld.Name = "Annuity" And DataType = "ThisData" Then
Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType
'get the last used row ***** Error is here *****
Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _
End(xlUp).Row
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow)
NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _
End(xlUp).Row
NLusedrow = NLusedrow + 2 'move past last item and leave 1 space
'between
Advisor and Annuity
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _
& ":C" &
NLusedrow + Lusedrow)
Lusedrow = 0
End If
If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then
Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _
DataType
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C11")
'remove the lapsed month
wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp
'add the new month data
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15")
Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15")
End If
End sub
For some reason the "Advisor" sections run fine. As soon as the ws name
changes to 'Annuity' I get a 'Method row of Object '_Global failed. wsOld
and wbkOld are set. Debug gives me the correct names. Is there something
about global variables I've missed?
Sub IDLocs(FromFileName, CopyFrom, CopyTo, DataType, FormatDateCell)
Dim Lusedrow As Long
Dim NLusedrow As Long
'Advisor
If wsOld.Name = "Advisor" And DataType = "ThisData" Then
Debug.Print "Starting Advisor ThisData " & DataType
'get the last used row
Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _
End(xlUp).Row
Debug.Print "Advisor ThisData Lusedrow " & Lusedrow
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow)
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C4:C" & Lusedrow + 2)
Lusedrow = 0
End If
'Debug.Print "wsOld.Name & DataType ThisVolume " & wsOld.Name & " " _
& DataType
If wsOld.Name = "Advisor" And DataType = "ThisVolume" Then
Debug.Print "Starting Advisor ThisVolume " & DataType
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C11")
'remove the lapsed month
wbkNew.Sheets(wsNew.Name).Range("K4:L4").Delete Shift:=xlUp
'add the new month data
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("K15:L15")
Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("K15")
End If
Debug.Print "wbkOld.name " & wbkOld.Name
'Annuity
If wsOld.Name = "Annuity" And DataType = "ThisData" Then
Debug.Print "Starting Annuity ThisData " & wsOld.Name & " " & DataType
'get the last used row ***** Error is here *****
Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B"). _
End(xlUp).Row
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("B2:B" & Lusedrow)
NLusedrow = wbkNew.Sheets(wsNew.Name).Cells(Rows.Count, "C"). _
End(xlUp).Row
NLusedrow = NLusedrow + 2 'move past last item and leave 1 space
'between
Advisor and Annuity
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow _
& ":C" &
NLusedrow + Lusedrow)
Lusedrow = 0
End If
If wsOld.Name = "Annuity" And DataType = "ThisVolume" Then
Debug.Print "Starting Annuity & ThisVolume " & wsOld.Name & " " & _
DataType
Set CopyFrom = wbkOld.Sheets(wsOld.Name).Range("C11")
'remove the lapsed month
wbkNew.Sheets(wsNew.Name).Range("H4:I4").Delete Shift:=xlUp
'add the new month data
Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("H15:I15")
Set FormatDateCell = wbkNew.Sheets(wsNew.Name).Range("H15")
End If
End sub