Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I cut instead of copy?
Hi
I am using some code to try and extract rows from one spreadsheet based on the value in a particular column matching that in another. The code i have at the moment will copy the row but not cut it also if there are 3 rows in my column matching the criteria it wont copy all three it will just take one. Do you know where the code is missing? Mary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
here is the code
Sorry this is the code I am using!!
Sub MoveMatches() Dim wsSrc As Worksheet Dim wsFind As Worksheet Dim wsDest As Worksheet Dim rCell As Range Dim rFound As Range Set wsSrc = Workbooks("1.xls").Sheets(1) Set wsFind = Workbooks("2.xls").Sheets(1) Set wsDest = Workbooks("2.xls").Sheets(2) For Each rCell In wsSrc.Columns(1).Cells Set rFound = wsFind.Columns(2).Find(rCell.Value) If Not rFound Is Nothing Then rCell.EntireRow.Cut wsDest.Range("A65536").End (xlUp).Offset(1, 0) End If Next rCell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
here is the code
try this
rCell.EntireRow.Cut destination:=wsDest.Range("A65536").End (xlUp).Offset(1, 0) you introduce <destination:= after <cut i.e. cut(space)destination:= I have not verified this . I have verified the following on the same sheet(activesheet) range("a1").EntireRow.Cut destination:=range("a10") Mary wrote in message ... Sorry this is the code I am using!! Sub MoveMatches() Dim wsSrc As Worksheet Dim wsFind As Worksheet Dim wsDest As Worksheet Dim rCell As Range Dim rFound As Range Set wsSrc = Workbooks("1.xls").Sheets(1) Set wsFind = Workbooks("2.xls").Sheets(1) Set wsDest = Workbooks("2.xls").Sheets(2) For Each rCell In wsSrc.Columns(1).Cells Set rFound = wsFind.Columns(2).Find(rCell.Value) If Not rFound Is Nothing Then rCell.EntireRow.Cut wsDest.Range("A65536").End (xlUp).Offset(1, 0) End If Next rCell End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
here is the code
The end result seems as though I have spent a long time
going through each row with the egg timer showing.. and then .. the row has been cut from 1.xls into 2.xls sheet2 I have another spreadsheet which I am using to hold the button on to click and start off the procedure. Could this be why there are problems? Where should the code be in a module? 1.xls? 2.xls? menu.xls? Thanks again -----Original Message----- try this rCell.EntireRow.Cut destination:=wsDest.Range ("A65536").End (xlUp).Offset(1, 0) you introduce <destination:= after <cut i.e. cut(space) destination:= I have not verified this . I have verified the following on the same sheet(activesheet) range("a1").EntireRow.Cut destination:=range("a10") Mary wrote in message ... Sorry this is the code I am using!! Sub MoveMatches() Dim wsSrc As Worksheet Dim wsFind As Worksheet Dim wsDest As Worksheet Dim rCell As Range Dim rFound As Range Set wsSrc = Workbooks("1.xls").Sheets(1) Set wsFind = Workbooks("2.xls").Sheets(1) Set wsDest = Workbooks("2.xls").Sheets(2) For Each rCell In wsSrc.Columns(1).Cells Set rFound = wsFind.Columns(2).Find (rCell.Value) If Not rFound Is Nothing Then rCell.EntireRow.Cut wsDest.Range("A65536").End (xlUp).Offset(1, 0) End If Next rCell End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming | |||
How copy format, font, color and border without copy/paste? | Excel Programming |