Moving entire row to new sheet help... Using a code I found through search, but it's not quit working.

G

GaryG9595

Board Regular
Joined
Jun 13, 2014
Messages
74
Office Version
  1. 365
Platform
  1. Windows
Hello I am trying to use a code I found on here, but I am running into a bit of a problem... It cuts the row from The 'data-dump' sheet, but I do not know where it is going. It's not going into the 'Archive' sheet that I created.

I basically have a few people working in a spreadsheet with data ( "Data-Dump" ) Columns A:DO
I want to fill in Column DH on 'Data-Dump' sheet with an "x" and have that entire row move to the next empty row on the 'Archive' sheet for later reference if needed starting in row 2 as it has the same headers as 'Data-Dump'

These rows from the 'DATA-DUMP' sheet have formulas as well, but they can and need to be just values on the 'Archive' sheet.

Sub Archive()
Dim lr As Long, lr2 As Long
lr = Worksheets("DATA-DUMP").UsedRange.Rows.Count
lr2 = Worksheets("Archive").UsedRange.Rows.Count
For Each cell In Range("DH1:DH" & lr)
If cell.Value = "x" Then
cell.EntireRow.Copy Destination:=Worksheets("Archive").Range("A2" & lr2 + 1)
cell.EntireRow.Delete
lr2 = Worksheets("Archive").UsedRange.Rows.Count
End If
Next cell
End Sub

Thanks in advance...
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
See if this does what you require (untested):
Code: 
Sub Archive()
Dim lr As Long, lr2 As Long, RngX As Range
lr = Worksheets("DATA-DUMP").Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lr2 = Worksheets("Archive").Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
With Worksheets("DATA-DUMP")
    .Range("DH1:DH" & lr).Replace "x", "#N/A"
    On Error Resume Next
    Set RngX = .Range("DH1:DH" & lr).SpecialCells(xlCellTypeConstants, xlErrors)
    On Error GoTo 0
    If RngX Is Nothing Then Exit Sub   'No cells marked with an "x"
    For Each cell In RngX
        cell.EntireRow.Copy
        With Worksheets("Archive")
            .Range("A" & lr2 + 1).PasteSpecial Paste:=xlValues
            lr2 = lr2 + 1
        End With
    Next cell
End With
RngX.EntireRow.Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0
GaryG9595 said:
Hello I am trying to use a code I found on here, but I am running into a bit of a problem... It cuts the row from The 'data-dump' sheet, but I do not know where it is going. It's not going into the 'Archive' sheet that I created.
...
Click to expand...


Hi GaryG9595.
. I was still checking out your code when Joe Mo replied. I tested his and the code works great and is a very interesting approach so I would stick with it. ... But just out of interest I think I spotted the mistake in yours....I think I know where your copied row is going... it is copied to Archive Sheet but down at around row 21, then 221, 2221 etc...
. You need to Change A2 to A in this line

Code: 
cell.EntireRow.Copy Destination:=Worksheets("Archive").Range("[color=red]A[/color]" & lr2 + 1)

Alan.

P.s. I think it is wise to use Option Explicit and dimension cell as range in your or Joe Mo’s code. But I am still a beginner so maybe Joe Mo can correct me if there is a reason for leaving cell as un dimensioned?? – I do not have much experience using For Each _____ loops
 
Upvote 0
Worked perfectly.... Thank you very much... The only thing I run into a problem is if they hide columns in the data-dump sheet, they obviously come over in the wrong columns in the archive sheet....
But i think I can figure that out, or just make sure they unhide the columns before archiving...
Thanks Again..... :)

JoeMo said:
See if this does what you require (untested):
Code: 
Sub Archive()
Dim lr As Long, lr2 As Long, RngX As Range
lr = Worksheets("DATA-DUMP").Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lr2 = Worksheets("Archive").Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
With Worksheets("DATA-DUMP")
    .Range("DH1:DH" & lr).Replace "x", "#N/A"
    On Error Resume Next
    Set RngX = .Range("DH1:DH" & lr).SpecialCells(xlCellTypeConstants, xlErrors)
    On Error GoTo 0
    If RngX Is Nothing Then Exit Sub   'No cells marked with an "x"
    For Each cell In RngX
        cell.EntireRow.Copy
        With Worksheets("Archive")
            .Range("A" & lr2 + 1).PasteSpecial Paste:=xlValues
            lr2 = lr2 + 1
        End With
    Next cell
End With
RngX.EntireRow.Delete
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Upvote 0
Update.... Hidden columns cunhide, just in case someone else might need it..... Works perfectly... Thanks again for your help...


Sub Archive()
Cells.Select
Range("CY1").Activate
Selection.EntireColumn.Hidden = False
Range("DH3").Select
Dim lr As Long, lr2 As Long, RngX As Range
lr = Worksheets("DATA-DUMP").Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lr2 = Worksheets("Archive").Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
With Worksheets("DATA-DUMP")
.Range("DH1:DH" & lr).Replace "x", "#N/A"
On Error Resume Next
Set RngX = .Range("DH1:DH" & lr).SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0
If RngX Is Nothing Then Exit Sub 'No cells marked with an "x"
For Each cell In RngX
cell.EntireRow.Copy
With Worksheets("Archive")
.Range("A" & lr2 + 1).PasteSpecial Paste:=xlValues
lr2 = lr2 + 1
End With
Next cell
End With
RngX.EntireRow.Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0
GaryG9595 said:
Worked perfectly.... Thank you very much... The only thing I run into a problem is if they hide columns in the data-dump sheet, they obviously come over in the wrong columns in the archive sheet....
But i think I can figure that out, or just make sure they unhide the columns before archiving...
Thanks Again..... :)
Click to expand...
You are welcome - thanks for the reply.
 
Upvote 0
@ GaryG9595.

GaryG9595 said:
Update.... Hidden columns cunhide, just in case someone else might need it..... Works perfectly... Thanks again for your help...
…..
Sub Archive()
Cells.Select
Range("CY1").Activate
Selection.EntireColumn.Hidden = False
Range("DH3").Select
...............
...........
Click to expand...

… Thanks for feeding back and adding to the Thread. Always makes for a good Thread.
.. To that end, and as I am practicing some other methods just now... I give two more codes at the end of this post that I think work like the others.. ( Have not done the Hidden columns cunhide bit )
. .. The codes are basically the same, ( - I just tried to explain everything in the second code with comments for further reference to remind me what is going on ). Apparently these codes should be very fast, as they use VBA Arrays, which I think is OK for you as you only are interested in values.. ). ( One last note: the code would fall down if there was only one value of x, because of the peculiarity of the .Transpose method which would returns 1 dimensional array in this case. This could be overcome if necessary by replacing the .Transpose function with a “true” transpose function.. see around Post #22 here
VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 ) - Page 2
For details.. )
....................................................................

@ JoeMo,

. Just in case you pop back to this Thread, a couple of follow up questions..

. a). From end of Post #3 – Just curious if there might have been a reason for not Dimensioning cell in your code..

. b) If you have time could you briefly explain your reasoning behind the method you chose. – I am keen to learn and have not seen this method before, and do not quite understand how it is working. ( I am googling vba SpecialCells(xlCellTypeConstants, xlErrors) just now, but the info as always is a bit overwhelming, - maybe you could explain it easily in plain English ?

. Thanks Alan

.....................................................................................

VBA “Array Method” codes:

Code: 
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] ArchiveApoAlanSimpflified()
 
[color=blue]Dim[/color] ws1 [color=blue]As[/color] Worksheet
[color=blue]Set[/color] ws1 = ActiveWorkbook.Worksheets("Data-Dump")
[color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Let[/color] lr = ws1.Cells.Find(What:="*", After:=ws1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
[color=blue]Dim[/color] arrin(): [color=blue]Let[/color] arrin() = ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, 119)).Value
[color=blue]Dim[/color] ws2 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws2 = ActiveWorkbook.Worksheets("Archive")
[color=blue]Dim[/color] lr2 [color=blue]As[/color] Long: [color=blue]Let[/color] lr2 = ws2.UsedRange.Rows.Count
 
[color=blue]Dim[/color] x(): x() = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Index(arrin(), 0, 112))
 
[color=blue]Dim[/color] strRows [color=blue]As[/color] [color=blue]String[/color], strRows2 [color=blue]As[/color] [color=blue]String[/color], iii [color=blue]As[/color] [color=blue]Long[/color]
       [color=blue]For[/color] iii = [color=blue]LBound[/color](x()) [color=blue]To[/color] [color=blue]UBound[/color](x())
           [color=blue]If[/color] x(iii) >= "x" [color=blue]Then[/color]
           strRows = strRows & " " & iii
           [color=blue]Else[/color]
           strRows2 = strRows2 & " " & iii
           [color=blue]End[/color] [color=blue]If[/color]
       [color=blue]Next[/color] iii
    [color=blue]Dim[/color] rws() [color=blue]As[/color] [color=blue]String[/color], rws2() [color=blue]As[/color] String: rws() = Split(Trim(strRows)): rws2() = Split(Trim(strRows2))
    [color=blue]Dim[/color] clms() [color=blue]As[/color] Variant: [color=blue]Let[/color] clms() = Evaluate("row(1:" & [color=blue]UBound[/color](arrin(), 2) & ")")
    [color=blue]Dim[/color] y(), y2(): y() = Application.WorksheetFunction.Transpose(Application.Index(arrin(), rws(), clms()))
    y2() = Application.WorksheetFunction.Transpose(Application.Index(arrin(), rws2(), clms()))
 
ws2.Range("A" & lr2 + 1 & "").Resize(UBound(y(), 1), UBound(y(), 2)).Value = y()
ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, 119)).ClearContents
ws1.Range("A2").Resize([color=blue]UBound[/color](y2(), 1), [color=blue]UBound[/color](y2(), 2)).Value = y2()
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'ArchiveApoAlanSHmpfGlified()[/color]
 
 
 
 
'
'
 
 
 
 
 
 [color=blue]Sub[/color] ArchiveApoAlan()
[color=lightgreen]' 1) Get Initial Data / info from Data Dump, and Archeive sheet[/color]
[color=blue]Dim[/color] ws1 [color=blue]As[/color] Worksheet [color=lightgreen]'Give Abbreviation Methods, Properties of Object Worksheet through .dot[/color]
[color=blue]Set[/color] ws1 = ActiveWorkbook.Worksheets("Data-Dump") [color=lightgreen]'Assumes Workbook is open and Active[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], lc [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Variable for Rows, Columns, last Row last Column of Dump sheet. Assume our File for Input has a reasonably well defined end. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]
[color=blue]Let[/color] lc = 119: [color=blue]Let[/color] lr = ws1.Cells.Find(What:="*", After:=ws1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row [color=lightgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), searching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method that finds last row in sheet rather than row for last entry in particular cell. Better to use that here as we are not sure which columns are full[/color]
[color=blue]Dim[/color] arrin() [color=blue]As[/color] Variant: [color=blue]Let[/color] arrin() = ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, lc)).Value [color=lightgreen]'Allowed VBA One Liner - An Array of variants may be set to a collection of Range values. The Range object works as to return a collection of (Variants initially) of various types. So Initially must see an Array of Variant types for compatability[/color]
[color=blue]Dim[/color] ws2 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws2 = ActiveWorkbook.Worksheets("Archive")
[color=blue]Dim[/color] lr2 [color=blue]As[/color] Long: [color=blue]Let[/color] lr2 = ws2.UsedRange.Rows.Count [color=lightgreen]'Count Property applied to Row Property of maximun range that inclides any entries of any kind already made in Archeive sheet[/color]
[color=lightgreen]' 2) Obtain a  I dimensional "pseudo" horizontal Array of LookUpColumn[/color]
[color=blue]Dim[/color] vLkUpc [color=blue]As[/color] Long: [color=blue]Let[/color] vLkUpc = 112 [color=lightgreen]'set column number 'Column where search criteria for filtering is. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]
    [color=blue]Dim[/color] x() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Dynamic one dimensional array for lookUpColumn[/color]
    [color=blue]Let[/color] x() = Application.WorksheetFunction.Index(arrin(), 0, vLkUpc) [color=lightgreen]'Returns format type (1,1) (2,1) (3,1) (4,1) >> Index Function with second argument (row co - ordinate) set to 0 will return the entire row given by first argument ( row - co ordinate ), applied to the first argument which is the grid, ( Array , Row_Number, Column_Number)  http://www.excelforum.com/excel-new-users-basics/1080634-vba-1-dimensional-horizontal-and-vertical-array-conventions-ha-1-2-3-4-a.html[/color]
    [color=blue]Let[/color] x() = Application.WorksheetFunction.Transpose(x()) [color=lightgreen]'working on 2 dimensional array of one column, conveniently by convenience returns 'Returns format type (1) (2) (3) (4) , a one dimension "psuedo" horizontal Array[/color]
 
[color=lightgreen]' 3) Obtain  Arrays of indicies for rows meeting and not meeting criteria for sort, and all colimn Indicies.[/color]
[color=blue]Dim[/color] strRows [color=blue]As[/color] [color=blue]String[/color], strRows2 [color=lightgreen]'Used as Temporary concatenated strings fo row indicies[/color]
    [color=blue]Dim[/color] iii [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Loop Bound (Count) variable[/color]
       [color=blue]For[/color] iii = [color=blue]LBound[/color](x()) [color=blue]To[/color] UBound(x()) [color=lightgreen]'Lower Bound by me is start of data[/color]
           [color=blue]If[/color] x(iii) = "x" [color=blue]Then[/color]
           strRows = strRows & " " & iii [color=lightgreen]'Concatenating valid "row" indicies[/color]
           [color=blue]Else[/color]
           strRows2 = strRows2 & " " & iii [color=lightgreen]'Concatenating invalid "row" indicies[/color]
           [color=blue]End[/color] [color=blue]If[/color]
       [color=blue]Next[/color] iii
       [color=blue]Let[/color] strRows = Trim(strRows) [color=lightgreen]'trim off first space[/color]
    [color=blue]Dim[/color] rws() [color=blue]As[/color] [color=blue]String[/color], rws2() [color=blue]As[/color] String: [color=blue]Let[/color] rws() = Split(strRows, " "): [color=blue]Let[/color] rws2() = Split(strRows2, " ") [color=lightgreen]'Finally rws and rws2 become 1 dimension1 "Psuedo" horizontal Arrays of the selected row inicia. The space is default, so " " could be ommited.. Split is a strings function usually returnig a String - Dim rws as variant would work , but not  Dim rws() as variant   From Post 48   http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays-4.html[/color]
   
    [color=blue]Dim[/color] clms() [color=blue]As[/color] Variant: [color=blue]Let[/color] clms() = Evaluate("row(1:" & UBound(arrin(), 2) & ")") [color=lightgreen]' 'Returns 1 column 2 dimensional array of size _: to :_  In that array are the number _:   to :_[/color]
 
[color=lightgreen]' 4 ) Obtain Full Column Output Arrays based on "row" selection criteria[/color]
    [color=blue]Dim[/color] y() [color=blue]As[/color] [color=blue]Variant[/color], y2() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'I believe here the Application.Index is working in some "vector" type form here. VBA "works" as follows here:[/color]
    [color=blue]Let[/color] y() = Application.Index(arrin(), rws(), clms()): [color=blue]Let[/color] y2() = Application.Index(arrin(), rws2(), clms()) [color=lightgreen]'It takes in turn each of the indicies in rws()[/color]
    [color=lightgreen]'and for each of these it steps through the indicies in clms(). It returns then effectively a "column" of values.[/color]
    [color=lightgreen]' These values are then the entities in the main Array arrin() given by those co-ordinates. In our case then, we initially put into the new Array y(), a column which contains the first data row.[/color]
    [color=blue]Let[/color] y() = Application.WorksheetFunction.Transpose(y()): [color=blue]Let[/color] y2() = Application.WorksheetFunction.Transpose(y2()) [color=lightgreen]'...As this process is then repeated for all the indicies given in  rws() we effectively have an Array  y() of our required output rows , but transposed. ( so we transpose it back to the correct orientation! )[/color]
    [color=lightgreen]' Note Application.WorksheetFunction.Index does not work here: Not sure why yet...http://www.excelforum.com/excel-new-users-basics/1080634-vba-1-dimensional-horizontal-and-vertical-array-conventions-ha-1-2-3-4-a.html[/color]
[color=lightgreen]' 5 ) Output results[/color]
[color=lightgreen]' 5a) Results to Archive[/color]
[color=blue]Let[/color] ws2.Range("A" & lr2 + 1 & "").Resize(UBound(y(), 1), UBound(y(), 2)).Value = y() [color=lightgreen]'A nice "One" liner - Resize selected cell to size of output Array and then the allowed VBA assignment of a collection of values to a Spreadsheet range[/color]
[color=lightgreen]' 5b) Replace Data-Dump with new data without Archeived data[/color]
ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, lc)).ClearContents
[color=blue]Let[/color] ws1.Range("A2").Resize(UBound(y2(), 1), UBound(y2(), 2)).Value = y2()
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'ArchiveApoAlan()[/color]
[color=lightgreen]'[/color]
'
'
'
 
Last edited:
Upvote 0
Re: Moving entire row to new sheet

Program No:DateDesign No:MeterWidthF1F2F3F4F5F6Base PickDescription, Change if anyMachine No:
45818/06/20158755051RedGreenRaniGold-zariBlackBlue48

<tbody>
</tbody>
I am complately new to VBA please help i have a workbook with sheet "program" and "running" and my requirement is when I enter any value in Column N2: from any given range, the row should cut and paste to sheet "Running"
 
Last edited:
Upvote 0
Re: Moving entire row to new sheet

. Hi jptsunil,

jptsunil said:
……..i have a workbook with sheet "program" and "running" and my requirement is when I enter any value in Column N2: from any given range, the row should cut and paste to sheet "Running"
Click to expand...

Welcome to the Board.
. possibly a border-line case of whether your request fits in tacked on to this Thread, but I guess the Thread Title suggest it, and could by future searches lead people with a similar request here.. so:- .
. Anyways:
.
... I assume test data looking something like this to start with:

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
1​
Program No:​
Date​
Design No:​
Meter​
Width​
F1​
F2​
F3​
F4​
F5​
F6​
Base Pick​
Description, Change if any​
Machine No:​
2​
458​
18.06.2015​
875​
50​
51​
Red​
Green​
Rani​
Gold-zari​
Black​
Blue​
48​
3​
459​
19.06.2015​
875​
50​
51​
Red​
Green​
Rani​
Gold-zari​
Black​
Blue​
49​
4​
460​
20.06.2015​
875​
50​
51​
Red​
Green​
Rani​
Gold-zari​
Black​
Blue​
50​
5​
461​
21.06.2015​
875​
50​
51​
Red​
Green​
Rani​
Gold-zari​
Black​
Blue​
51​

<tbody>
</tbody>
program

<tbody>
</tbody>
.
... and your running sheet has the headings already there.

.........................

... if then for example I type this in a couple of cells in the N column:

Using Excel 2007
Row\Col
M​
N​
1​
Description, Change if any​
Machine No:​
2​
3​
go​
4​
5​
go​
6​

<tbody>
</tbody>
program

<tbody>
</tbody>

.... then I get this in the running sheet

Using Excel 2007
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
1​
Program No:​
Date​
Design No:​
Meter​
Width​
F1​
F2​
F3​
F4​
F5​
F6​
Base Pick​
Description, Change if any​
Machine No:​
2​
459​
19.06.2015​
875​
50​
51​
Red​
Green​
Rani​
Gold-zari​
Black​
Blue​
49​
go​
3​
461​
21.06.2015​
875​
50​
51​
Red​
Green​
Rani​
Gold-zari​
Black​
Blue​
51​
go​
4​

<tbody>
</tbody>
running

<tbody>
</tbody>

.....
. To achieve this I wrote the following code and placed it in the sheet module for sheet “program”


Code: 
[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
[COLOR=lightgreen]'_______________________________________________________________________________________-[/COLOR]
[COLOR=lightgreen]'_______________________________________________________________________________[/COLOR]
 
[COLOR=lightgreen]'_______________________________________________________________________________________________-[/COLOR]
[COLOR=lightgreen]'[/COLOR]
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Worksheet_Change([COLOR=blue]ByVal[/COLOR] Target [COLOR=blue]As[/COLOR] Range) 'What happens when worksheet is Changed:-
[COLOR=blue]Dim[/COLOR] wsr [COLOR=blue]As[/COLOR] Worksheet: [COLOR=blue]Set[/COLOR] wsr = ThisWorkbook.Worksheets("Running") [COLOR=lightgreen]'Give abbreviation Methods, Properties, etc. of worksheets Object obtainable through the .Dot[/COLOR]
[COLOR=blue]Dim[/COLOR] wksHere [COLOR=blue]As[/COLOR] Worksheet: [COLOR=blue]Set[/COLOR] wksHere = ThisWorkbook.Worksheets("program") [COLOR=lightgreen]'Mostly redundant as code is ( must be ) in This sheet Module usually[/COLOR]
[COLOR=blue]Dim[/COLOR] rngAim [COLOR=blue]As[/COLOR] Range [COLOR=lightgreen]'The range where you want an input to set off the code.[/COLOR]
[COLOR=blue]Dim[/COLOR] rr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], rp [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], lrr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], lrp [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] [COLOR=lightgreen]'Variable for rows, last rows in sheets. Assume our File has a reasonably well defined end. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/COLOR]
[COLOR=blue]Let[/COLOR] lrp = wksHere.Cells(Rows.Count, "A").End(xlUp).Row [COLOR=lightgreen]'The [COLOR=blue]End[/COLOR] property (with argument Up) applied to last cell in coluimn A returns a new Range ( cell ) where an entry is found, which in turn has the row property applied to return the row of that range ( cell )[/COLOR]
[COLOR=blue]Set[/COLOR] rngAim = wksHere.Range("N2:N" & lrp & "") [COLOR=lightgreen]'Set up Target Range as N column staring at 2 and going down to last entry.[/COLOR]
    [COLOR=blue]If[/COLOR] Intersect(rngAim, Target) [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]  [COLOR=lightgreen]' Not too sure why this particular way is chosen, possibly Professionals know from experience it works best often??[/COLOR]
    [COLOR=lightgreen]'Case you did not type in rngAim - Do nothing, - end of if - end of sub[/COLOR]
    [COLOR=blue]Else[/COLOR] [COLOR=lightgreen]' case you targeted rng Aim[/COLOR]
    [COLOR=blue]Let[/COLOR] lrr = wsr.Cells.Find(What:="*", After:=wsr.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [COLOR=lightgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), searching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method that finds last row in sheet rather than row for last entry in particular cell.[/COLOR]
    Target.Offset(0, -13).Resize(1, 14).Copy [COLOR=lightgreen]'Take a range made from a cell offset 12 to the left from your targetteg cell and resized to 14 columns[/COLOR]
    wsr.Range("A" & lrr + 1 & "").PasteSpecial xlPasteAllUsingSourceTheme [COLOR=lightgreen]'Select first free cell in column A of running sheet and paste in from clipboard. A different argument may be appropriate: Post #25 http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value-3.html[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
Application.CutCopyMode = [COLOR=blue]False[/COLOR] [COLOR=lightgreen]'Stop screen flicker after paste[/COLOR]
End [COLOR=blue]Sub[/COLOR]

.............................
.. Here is the file: ( XL 2007 )
https://app.box.com/s/ikq5cbrlfoxfd0bmgtzu2kqkm271mj37

. Let me know please how you get on..
Alan
 
Last edited:
Upvote 0
Re: Moving entire row to new sheet

Thank you verymuchDocaElstein it works but it copies and pastes the row, I need cut and paste. sorry for my bad English.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Code to move rows to a new sheet.
Replies
4
Views
413
dandee14k
D
tabbytomo
  • Solved
Copy entire row if cell has specific value, looking at multiple columns and worksheets.
Replies
2
Views
281
NateSC
NateSC
K
  • Solved
VBA Help!!! Paste Special Value based on Existing Code
Replies
3
Views
320
Armstrong_N14
A
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
228
birdman15
B
R
  • Question
VBA - Automatically move rows to another tab based on text in cell.
Replies
0
Views
782
Rzr23
R

Forum statistics

Threads
1,214,653
Messages
6,120,755
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top