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

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

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