| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy selected rows to second worksheet (NOT Cut + Paste) | New Users to Excel | |||
| Copy a selected number of rows | Excel Discussion (Misc queries) | |||
| Is it possible to copy only selected items to another worksheet... | Excel Worksheet Functions | |||
| copy selected rows to new sheet | Excel Programming | |||
| Macro to copy and past selected rows only... | Excel Programming | |||
Linear Mode
