Need an explanation

[Guest]

The following code was working fine until I uninstalled my printer and
reinstalled it so my offbrand ink cartridges would work. This should have
had no bearing on the code since all files were closed when I did the printer
thing. However, that was the only change I had made that I can remember.

These are the two lines that it does not like.

For Each c In Worksheets(1).Range(Cells(2, 3), Cells(lr, 3))

Worksheets(1).Range(Cells(r, 1), Cells(r, 3)).Copy

I change the Range references to:

Range("$C$2:$C" & lr) and Range("$A" & r & ":$C" & r) respectively
and it works fine again. What happened to the other method of Range?
Why would it suddenly decide not to recognize that code?

Private Sub may_Click()
Unload WOListFrm
lr = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(1).Range("$A$1:$C$1").Copy Worksheets(4).Range("$A$1")
Application.CutCopyMode = False
stDate = "5/1"
stDate = Format(stDate, "m/d")
endDate = "5/31"
endDate = Format(endDate, "m/d")
For Each c In Worksheets(1).Range(Cells(2, 3), Cells(lr, 3))
If Not c Is Nothing Then
sRng = c.Address
End If
If c >= stDate And c <= endDate Then
r = Range(sRng).Row
Worksheets(1).Range(Cells(r, 1), Cells(r, 3)).Copy
prepRpt
Application.CutCopyMode = False
End If
Next
If Worksheets(4).Range("$A2") = "" Then
TitleScrn
MsgBox "NO WORK ORDERS TO REPORT.", , "ADVISORY"
Worksheets(4).Cells.Clear
Exit Sub
End If
Worksheets(4).PrintOut
Worksheets(4).Cells.Clear
Worksheets(4).Columns.UseStandardWidth = True
MsgBox "Report has printed", vbInformation, "ADVISORY"
End Sub

 

[Guest]

The success of this code
For Each c In Worksheets(1).Range(Cells(2, 3), Cells(lr, 3))

Worksheets(1).Range(Cells(r, 1), Cells(r, 3)).Copy

is dependent on what sheet is active when the code is run.

If the range is on worksheets(1) then it should be written as

With Worksheets(1)
For Each c In .Range(.Cells(2, 3), .Cells(lr, 3))
If Not c Is Nothing Then
sRng = c.Address
End If
If c >= stDate And c <= endDate Then
r = Range(sRng).Row
.Range(.Cells(r, 1), .Cells(r, 3)).Copy
prepRpt
Application.CutCopyMode = False
End If
Next
End With

the other method of addressing only refers to one sheet.

 

[Guest]

Thanks, Tom. After thinking it over, I did make a change moving the title
page which has the command button to start the program from sheet one to
sheet five, so that ties it up. It sure had me confused.