Can you post the portion of the code that fails?

Remember to include any values that the the procedure depends on.

Karen53 wrote:

Dave,

Ok, I got it. I have to select the procedure that declared the variable.

I found the sort problem and it has been fixed. But I am still erroring at
the Lusedrow, so they were unrelated.
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

If you were getting a "subscript out of error" message, I bet dollar to
doughnuts that you had something wrong--a typo or an unqualified reference.

I don't recall ever getting that error when it wasn't my fault.

When you're debugging your code, you can add some of those variables to the
watch window and step through the code while keeping an eye on those variables.
It might make things a little clearer than the debug.print statements.

Karen53 wrote:

Dave,

I'll try that. In stepping through the code I recall everything being
correct, but I ccould be remembering incorrectly. The code doesn't get as
far as actually getting the Lusedrow and Nlusedrow for 'Annuity'.

I suspect the problem is actually elsewhere. It's so frustrating when the
errors point everywhere but where the error actually is. I had code to sort
the range set to run prior to this which would be the 'Advisor' section and
that was erroring out at well. The sort was giving me subscript out of range
when it wasn't. I removed it to try and pinpoint the problem. Then
'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead.

Just to cover these bases, here is the code for my global variables and
setting wsOld(thanks to you!)...

Option Explicit

'module modCopyData

Public wsNew As Worksheet
Public wsOld As Worksheet
Public wbkOld As Workbook
Public wbkNew As Workbook
Public OldJustPath As String
Public OldwbkPath As Variant

Sub SetWkShtVariable(OldShName)

Dim wks As Worksheet

Debug.Print "Starting SetwkShtVariable"

Set wsOld = Nothing
Set wks = Nothing

For Each wks In wbkOld.Worksheets
If LCase(wks.CodeName) = LCase(OldShName) Then
'found it
Set wsOld = wks
Exit For 'stop looking
End If
Next wks

If wsOld Is Nothing Then
'not found!
Else
MsgBox wsOld.Name & vbLf & wsOld.CodeName
Debug.Print "wsOld.Name " & wsOld.Name & " wsold.codename " _
&
wsOld.CodeName
End If

End Sub

Here is the code calling the procedures. If I leave the sort in it errors
at the sort code in the 'Call CopyToRange' in the first grouping ,"ThisData",
of calls.

For iCtr = 0 To 2

Debug.Print "Start " & SheetRangearray(iCtr)

DataType = "ThisData"
OldShName = SheetRangearray(iCtr)
Call CrackedID(OldShName, FileName, DataType, StartCol, _
EndCol, SumCol, StartRow,
EndRow)
Call SetWkShtVariable(OldShName)
Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _

rngFormatDateCell)
Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _
DataType, StartCol, EndCol, StartRow,
EndRow)
Call SetIDGraphs(OldShName, EndRow)
DataType = ""

DataType = "ThisVolume"
Call CrackedID(OldShName, FileName, DataType, StartCol, _
EndCol, SumCol, StartRow, EndRow)
Call IDLocs(FileName, rngCopyFrom, rngCopyTo, DataType, _

rngFormatDateCell)
Call CopyToRange(rngCopyFrom, rngCopyTo, rngFormatDateCell, _
DataType, StartCol, EndCol, StartRow,
EndRow)
Call SetIDGraphs(OldShName, EndRow)
DataType = ""
OldShName = ""
Next

--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

First, if wsOld is part of the WbkOld workbook, you don't need code like:
Lusedrow = wbkOld.Sheets(wsOld.Name).Cells(Rows.Count, "B").End(xlUp).Row

you can refer to wsold directly
Lusedrow = wsOld.Cells(Rows.Count, "B").End(xlUp).Row

I think it makes the code easier to read (and write).

Second, I don't see anything that jumps out with a problem. But that doesn't
mean much.

The suggestion I would offer is to add a debug.print line right before the line
that causes the error. But print all the variables and their values so you know
what's going on.

So if this line is causing the trouble:

Set CopyTo = wbkNew.Sheets(wsNew.Name).Range("C" & NLusedrow & ":C" _
& NLusedrow + Lusedrow)
Or

Set CopyTo = wsNew.Range("C" & NLusedrow & ":C" & NLusedrow + Lusedrow)

Then add this before that line:

debug.print "nlusedrow: " & nlusedrow & vblf & "Lusedrow: " & lusedrow

Maybe something is not returning the value you think.

Karen53 wrote:

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("C1:D1")

'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("C1:D1")

'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

--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson