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 -