Sub CpyPst()

Dim sh As Worksheet, rgnRange As Range, lRow As Long
Set rgnRange = Sheets("Region Summary").Range("B2:B15")
For Each sh In Worksheets
With sh
If .Name < "Region Summary" And .Name < "Rep Summary" Then
If WorksheetFunction.CountIf(rgnRange, _
..Range("B2")) 0 Then
..Range("B2").Offset(1, 0).EntireRow.Copy

..Range("A65536") _
..End(xlUp).Offset(2, 0).PasteSpecial xlValues
End If
End If
End With
Next
Set rgnRange = Nothing

End Sub


I modified this macro and now it does some weird stuff. � It copies the
3rd row from each sheet, goes to the bottom, back up to the used range,
offsets 2, and pastes it there. What I wanted to do was look at values in
Column B in each sheet (not named Region Summary or Rep Summary) and find
matches in Column B in the region Summary sheet, and then, if there is a
match, paste/special, go to the bottom, back up to the used range, offsets 2,
and pastes it there. But even before this, I wanted to copy/paste the range
form A1:O1 on Region Summary to each sheet not named Region Summary or Rep
Summary. Finally, I wanted to bold this range that is copied to each sheet.
It is easy to do when the rows are fixed, but Im not sure how to do it when
the rows are variable.

Sorry about this guys. The loops always get me! I look forward to the day
that I can get them!!

I'll take a few more cracks at it, and try to resolve it on my own, but if
you have time JW, I'd be grateful if you could get back to me with the answer.

Regards,
Ryan---

--
RyGuy


"JW" wrote:

Paste where in each sheet? This will copy A1:O1 of Region and place
it in the first available row in column A of every sheet not named
Region ot Rep Summary.
Sub ryguy7272_2()
Dim copyRange As Range, sh As Worksheet
Set copyRange = Sheets("Region").Range("A1:O1")
copyRange.Copy
For Each sh In Worksheets
With sh
If .Name < "Region" And _
.Name < "Rep Summary" Then _
.Range("A65536").End(xlUp) _
.Offset(1, 0).PasteSpecial xlValues
End With
Next sh
Application.CutCopyMode = False
Set copyRange = Nothing
End Sub


On May 12, 10:38 am, ryguy7272
wrote:
Oh My!!! Now I see the logic!! I was trying to do this complicated
Index/Match routine with an Offset...totalyl wrong approach. This is exactly
what I need. One more thing, how can I copy the range A1:O1 on "Region" and
dynamically offset paste to each sheet if the name is not "Region" or "Rep
Summary"?

Thanks so much!!
Ryan--

--
RyGuy



"JW" wrote:
Several different ways this could be done. Instead of looping through
the range in the Region sheet, I believe it would be more effeciant to
just use a CountIf function to see if it occurs in the range. You
didn't mention where you wanted the row 2 rows down to be copied to,
so this just sticks it in the first available row (based on Column A)
in Sheet1. Tweak as needed.
Sub ryguy7272()
Dim sh As Worksheet, rgnRange As Range, lRow As Long
Set rgnRange = Sheets("Region").Range("B2:B15")
For Each sh In Worksheets
With sh
If .Name < "Region" And .Name < "Sheet1" Then
If WorksheetFunction.CountIf(rgnRange, _
.Range("B2")) 0 Then
.Range("B2").Offset(2, 0).EntireRow.Copy
Sheets("Sheet1").Range("A65536") _
.End(xlUp).Offset(1, 0).PasteSpecial
xlValues
End If
End If
End With
Next
Set rgnRange = Nothing
End Sub

On May 12, 9:24 am, ryguy7272
wrote:
I am trying to come up with a way of looping through all B2 cells in all
sheets and comparing the value to the values in a variable range, such as
B2:B15, on a specific sheet named €œRegion€�, and if there is a match, copy
paste the entire row (just one row) 2 lines below the match. Challenging?
Yes!! Possible? I don't know but I hope so!!

For next code below:

For Each sh In Worksheets
If LCase(sh.Name) < LCase("Sheet1") Then
sh.Activate
With

€˜Stuff€¦

End with

End If
Next sh
End Sub

Regards,
Ryan---

--
RyGuy- Hide quoted text -

- Show quoted text -