Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Specific Rows and pasting to new worksheet
Good morning,
I have to worksheets in my workbook, "uncleanded" and "reportable" In the uncleaned worksheet I have a number of transactions. What I am trying to do is find, copy and paste specific transactions into the second worksheet. Current code: Dim c As Range Dim SrchRng Sheets("Uncleaned").Select Set SrchRng = ActiveSheet.Range("H1", ActiveSheet.Range("A1000").End(xlUp)) Do Set c = SrchRng.Find("Coke", LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Cut Sheets("Reportable").Select ActiveSheet.Paste Loop While Not c Is Nothing Unforetunately, the code currently has two major flaws: 1. it simply copies into row1 on worksheet "reportable" 2. it crashes at the end. "Run-time error '1004': Paste method of Worksheet class failed" I have been trying, without success, to find solutions for these two issues (which I am sue are quite simple mistakes). Please can someone help me with this. thanks in advance, I really do appreciate the help Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Specific Rows and pasting to new worksheet
Hi,
try this alternative approach Sub Try_This() Dim c As Range Dim SrchRng As Range Set SrchRng = Sheets("Uncleaned").Range("H1", _ Sheets("Uncleaned").Range("A1000").End(xlUp)) For Each c In SrchRng If UCase(c.Value) = "COKE" Then lastrow = Sheets("Reportable").Cells(Cells.Rows.Count, _ "A").End(xlUp).Row + 1 c.EntireRow.Copy Destination:=Sheets("Reportable").Range("A" _ & lastrow) c.EntireRow.Delete End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good morning, I have to worksheets in my workbook, "uncleanded" and "reportable" In the uncleaned worksheet I have a number of transactions. What I am trying to do is find, copy and paste specific transactions into the second worksheet. Current code: Dim c As Range Dim SrchRng Sheets("Uncleaned").Select Set SrchRng = ActiveSheet.Range("H1", ActiveSheet.Range("A1000").End(xlUp)) Do Set c = SrchRng.Find("Coke", LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Cut Sheets("Reportable").Select ActiveSheet.Paste Loop While Not c Is Nothing Unforetunately, the code currently has two major flaws: 1. it simply copies into row1 on worksheet "reportable" 2. it crashes at the end. "Run-time error '1004': Paste method of Worksheet class failed" I have been trying, without success, to find solutions for these two issues (which I am sue are quite simple mistakes). Please can someone help me with this. thanks in advance, I really do appreciate the help Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Specific Rows and pasting to new worksheet
Thanks Mike,
Works like a charm Regards, "Mike H" wrote: Hi, try this alternative approach Sub Try_This() Dim c As Range Dim SrchRng As Range Set SrchRng = Sheets("Uncleaned").Range("H1", _ Sheets("Uncleaned").Range("A1000").End(xlUp)) For Each c In SrchRng If UCase(c.Value) = "COKE" Then lastrow = Sheets("Reportable").Cells(Cells.Rows.Count, _ "A").End(xlUp).Row + 1 c.EntireRow.Copy Destination:=Sheets("Reportable").Range("A" _ & lastrow) c.EntireRow.Delete End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good morning, I have to worksheets in my workbook, "uncleanded" and "reportable" In the uncleaned worksheet I have a number of transactions. What I am trying to do is find, copy and paste specific transactions into the second worksheet. Current code: Dim c As Range Dim SrchRng Sheets("Uncleaned").Select Set SrchRng = ActiveSheet.Range("H1", ActiveSheet.Range("A1000").End(xlUp)) Do Set c = SrchRng.Find("Coke", LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Cut Sheets("Reportable").Select ActiveSheet.Paste Loop While Not c Is Nothing Unforetunately, the code currently has two major flaws: 1. it simply copies into row1 on worksheet "reportable" 2. it crashes at the end. "Run-time error '1004': Paste method of Worksheet class failed" I have been trying, without success, to find solutions for these two issues (which I am sue are quite simple mistakes). Please can someone help me with this. thanks in advance, I really do appreciate the help Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Specific Rows and pasting to new worksheet
Glad I could help and thanks for the feedback
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Thanks Mike, Works like a charm Regards, "Mike H" wrote: Hi, try this alternative approach Sub Try_This() Dim c As Range Dim SrchRng As Range Set SrchRng = Sheets("Uncleaned").Range("H1", _ Sheets("Uncleaned").Range("A1000").End(xlUp)) For Each c In SrchRng If UCase(c.Value) = "COKE" Then lastrow = Sheets("Reportable").Cells(Cells.Rows.Count, _ "A").End(xlUp).Row + 1 c.EntireRow.Copy Destination:=Sheets("Reportable").Range("A" _ & lastrow) c.EntireRow.Delete End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Good morning, I have to worksheets in my workbook, "uncleanded" and "reportable" In the uncleaned worksheet I have a number of transactions. What I am trying to do is find, copy and paste specific transactions into the second worksheet. Current code: Dim c As Range Dim SrchRng Sheets("Uncleaned").Select Set SrchRng = ActiveSheet.Range("H1", ActiveSheet.Range("A1000").End(xlUp)) Do Set c = SrchRng.Find("Coke", LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Cut Sheets("Reportable").Select ActiveSheet.Paste Loop While Not c Is Nothing Unforetunately, the code currently has two major flaws: 1. it simply copies into row1 on worksheet "reportable" 2. it crashes at the end. "Run-time error '1004': Paste method of Worksheet class failed" I have been trying, without success, to find solutions for these two issues (which I am sue are quite simple mistakes). Please can someone help me with this. thanks in advance, I really do appreciate the help Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying, Pasting Autofilter results to a region more specific than a specified worksheet. | Excel Programming | |||
finding the number of rows and the first row with a specific value | Excel Programming | |||
finding the number of rows and the first row with a specific value | Excel Programming | |||
Copy and pasting specific sheet data to a master worksheet | Excel Programming | |||
finding the number of rows and the first row with a specific value | Excel Programming |