copy selected rows to a new worksheet
 

I want to save rows that match a certain criteria to a new worksheet. When I
complete the task, I want to save that worksheet to a file.

I create the worksheet like this:

Set new_xls = Worksheets.Add
new_xls.Move after:=Worksheets(Worksheets.Count)

The move is to put it at the end of the workbook, though I suppose I could
have done it in a single line of code when I created it.

The problem I have is that when I select the data and copy it only works the
first time through the loop. I get an appication error the second time I
paste. (The first time things seem to work properly.

For Each qb In qb_range
If qb.Offset(0, 13) rav_rat Then

better = better + 1#
qb.EntireRow.Copy
ActiveSheet.Paste Destination:=new_xls.Cells(1, Int(better))
If qb_list = "" Then
qb_list = qb_list & Mid(qb.Value, 1, Len(qb.Value) - 7) &
vbCrLf
Else
qb_list = qb_list & " &" & Mid(qb.Value, 1, Len(qb.Value) -
7) & vbCrLf
End If

Else
worse = worse + 1#
End If
Next qb

What am I doing wrong?

Thanks,
David

copy selected rows to a new worksheet
 

You can give this a try...

For Each qb In qb_range
If qb.Offset(0, 13) rav_rat Then

better = better + 1
qb.EntireRow.Copy Destination:=new_xls.Cells(better, 1)
If qb_list = "" Then
qb_list = qb_list & Mid(qb.Value, 1, Len(qb.Value) - 7) &
vbCrLf
Else
qb_list = qb_list & " &" & Mid(qb.Value, 1, Len(qb.Value) -
7) & vbCrLf
End If

Else
worse = worse + 1#
End If
Next qb

--
HTH...

Jim Thomlinson


"David Gerstman" wrote:

I want to save rows that match a certain criteria to a new worksheet. When I
complete the task, I want to save that worksheet to a file.

I create the worksheet like this:

Set new_xls = Worksheets.Add
new_xls.Move after:=Worksheets(Worksheets.Count)

The move is to put it at the end of the workbook, though I suppose I could
have done it in a single line of code when I created it.

The problem I have is that when I select the data and copy it only works the
first time through the loop. I get an appication error the second time I
paste. (The first time things seem to work properly.

For Each qb In qb_range
If qb.Offset(0, 13) rav_rat Then

better = better + 1#
qb.EntireRow.Copy
ActiveSheet.Paste Destination:=new_xls.Cells(1, Int(better))
If qb_list = "" Then
qb_list = qb_list & Mid(qb.Value, 1, Len(qb.Value) - 7) &
vbCrLf
Else
qb_list = qb_list & " &" & Mid(qb.Value, 1, Len(qb.Value) -
7) & vbCrLf
End If

Else
worse = worse + 1#
End If
Next qb

What am I doing wrong?

Thanks,
David

copy selected rows to a new worksheet
 

Thank you. So in paste I confused row and column.
Sigh.
Again many thanks,
David

"Jim Thomlinson" wrote:

You can give this a try...

For Each qb In qb_range
If qb.Offset(0, 13) rav_rat Then

better = better + 1
qb.EntireRow.Copy Destination:=new_xls.Cells(better, 1)
If qb_list = "" Then
qb_list = qb_list & Mid(qb.Value, 1, Len(qb.Value) - 7) &
vbCrLf
Else
qb_list = qb_list & " &" & Mid(qb.Value, 1, Len(qb.Value) -
7) & vbCrLf
End If

Else
worse = worse + 1#
End If
Next qb

--
HTH...

Jim Thomlinson


"David Gerstman" wrote:

I want to save rows that match a certain criteria to a new worksheet. When I
complete the task, I want to save that worksheet to a file.

I create the worksheet like this:

Set new_xls = Worksheets.Add
new_xls.Move after:=Worksheets(Worksheets.Count)

The move is to put it at the end of the workbook, though I suppose I could
have done it in a single line of code when I created it.

The problem I have is that when I select the data and copy it only works the
first time through the loop. I get an appication error the second time I
paste. (The first time things seem to work properly.

For Each qb In qb_range
If qb.Offset(0, 13) rav_rat Then

better = better + 1#
qb.EntireRow.Copy
ActiveSheet.Paste Destination:=new_xls.Cells(1, Int(better))
If qb_list = "" Then
qb_list = qb_list & Mid(qb.Value, 1, Len(qb.Value) - 7) &
vbCrLf
Else
qb_list = qb_list & " &" & Mid(qb.Value, 1, Len(qb.Value) -
7) & vbCrLf
End If

Else
worse = worse + 1#
End If
Next qb

What am I doing wrong?

Thanks,
David