Referencing a cell in VBA from a different worksheet

[Patel]

Hello,

I originally wrote this code for data that was on one sheet, but now I
want to look at data that is on Sheet 1 and if it fits the criteria
that I have listed on Sheet 2, then I will go through the j loop and
copy it. I don't know how to reference the cells in the 1st "if"
statement when they are on different sheets. Can someone please help
me?

Sub trial()
For k = 2 To 9
If Cells(k, 12) = Cells(17, 4) And Cells(k, 5) >= Cells(17, 2)
And Cells(k, 5) <= Cells(17, 3) Then
For j = 1 To 26
Cells(k + 16, j) = Cells(k, j)
Next j
End If
Next k
End Sub



Thanks,
Stephen

 

[Dave Peterson]

Maybe...

Option Explicit
Sub trial()

Dim Wks1 As Worksheet
Dim Wks2 As Worksheet

Set Wks1 = Worksheets("sheet1")
Set Wks2 = Worksheets("sheet2")

For k = 2 To 9
If Wks1.Cells(k, 12).Value = Wks2.Cells(17, 4).Value _
And Wks1.Cells(k, 5).Value >= Wks2.Cells(17, 2).Value _
And Wks1.Cells(k, 5).Value <= Wks2.Cells(17, 3).Value Then
For j = 1 To 26
Wks1.Cells(k + 16, j).Value = Wks1.Cells(k, j).Value
Next j
End If
Next k

End Sub

 

[Bob Phillips]

Which parts are Sheet1 and which Sheet2?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)