i have a summary workbook and for some reason i can't get it to paste the
values from the source workbook. i open the workbook, it seems to go through
all of the cells, but it never matches my criteria and pastes the value. can
some see what is worng?


With Workbooks(Fname).Worksheets(sh) ' this is the source
For sh = 1 To 2 ' only 2 sheets to test with
Set rng = .Range("B3:B33") ' range to look for the value in the if statement
For Each cell In rng
If UCase(cell.Value) = "S" Then
.cell.Offset(0, 1).Copy ' if true, copy the number in column C and
paste it to F4 in the summary workbook
Workbooks(Curbook).Worksheets(Cursheet).Range("f4" ).PasteSpecial _
xlValues, operation:=xlAdd
End If
Next cell
Next sh
End With

--


Gary

DIM WS as Worksheet
SET WS = Workbooks(Curbook).Worksheets(Cursheet)
With Workbooks(Fname).Worksheets(sh) ' this is the source
For sh = 1 To 2 ' only 2 sheets to test with
Set rng = .Range("B3:B33") ' range to look for the value in the if statement
For Each cell In rng.Cells
If UCase(cell.Value) = "S" Then
WS.Range("f4").Value = WS.Range("f4").Value + .cell.Offset(0,
1).Value
End If
Next cell
Next sh
End With



"Gary Keramidas" wrote:

i have a summary workbook and for some reason i can't get it to paste the
values from the source workbook. i open the workbook, it seems to go through
all of the cells, but it never matches my criteria and pastes the value. can
some see what is worng?


With Workbooks(Fname).Worksheets(sh) ' this is the source
For sh = 1 To 2 ' only 2 sheets to test with
Set rng = .Range("B3:B33") ' range to look for the value in the if statement
For Each cell In rng
If UCase(cell.Value) = "S" Then
.cell.Offset(0, 1).Copy ' if true, copy the number in column C and
paste it to F4 in the summary workbook
Workbooks(Curbook).Worksheets(Cursheet).Range("f4" ).PasteSpecial _
xlValues, operation:=xlAdd
End If
Next cell
Next sh
End With

--


Gary

thanks patrick, i was able to get it working with your help. i think all i
had to do was remove the period before the word range (Set rng =
Range("B3:B33")), which i had in my original post.

thanks again

--


Gary


"Patrick Molloy" wrote in message
...
DIM WS as Worksheet
SET WS = Workbooks(Curbook).Worksheets(Cursheet)
With Workbooks(Fname).Worksheets(sh) ' this is the source
For sh = 1 To 2 ' only 2 sheets to test with
Set rng = .Range("B3:B33") ' range to look for the value in the if
statement
For Each cell In rng.Cells
If UCase(cell.Value) = "S" Then
WS.Range("f4").Value = WS.Range("f4").Value + .cell.Offset(0,
1).Value
End If
Next cell
Next sh
End With



"Gary Keramidas" wrote:

i have a summary workbook and for some reason i can't get it to paste the
values from the source workbook. i open the workbook, it seems to go
through
all of the cells, but it never matches my criteria and pastes the value.
can
some see what is worng?


With Workbooks(Fname).Worksheets(sh) ' this is the source
For sh = 1 To 2 ' only 2 sheets to test with
Set rng = .Range("B3:B33") ' range to look for the value in the if
statement
For Each cell In rng
If UCase(cell.Value) = "S" Then
.cell.Offset(0, 1).Copy ' if true, copy the number in column C
and
paste it to F4 in the summary workbook
Workbooks(Curbook).Worksheets(Cursheet).Range("f4" ).PasteSpecial
_
xlValues, operation:=xlAdd
End If
Next cell
Next sh
End With

--


Gary