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

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.

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote:

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

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.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote:

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