This does work. Thank you very much.

"Jim Thomlinson" wrote:

untested but this should work...

Dim wks As worksheet
For Each wks In Worksheets
On Error Resume Next
wks.Activate
If wks.Name < "IB_Status" Then
wks.Select
Range("A2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("IB_Status"). Select
Sheets("IB_Status").cells(rows.count, 1).end(xlup).offset(1,0).select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
Next wks

--
HTH...

Jim Thomlinson


"Judy Ward" wrote:

I have a macro copies all the rows from sheets that are not named "IB_Status"
to the "IB_Status" sheet. The macro worked great when all I had to do was
insert the rows (I could select row 2 and shift any existing rows down). Now
it turns out that I need to copy and paste the values (since I don't want to
copy the formulas). I need to figure out how to select the first blank row
in "IB_Status":

Dim wks As Object
For Each wks In Worksheets
On Error Resume Next
wks.Activate
If wks.Name < "IB_Status" Then
wks.Select
Range("A2:G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("IB_Status"). Select
' I need to paste values rather than insert
' Need code to select the first blank row here
' Rows("2:2").Select --Old code
' Selection.Insert Shift:=xlDown -Old code
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
Next wks

I would appreciate any help you can give me.
Thank you,
Judy