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

GaryG9595

New Member
Joined
Jun 13, 2014
Messages
48
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...
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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.
...


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
 

GaryG9595

New Member
Joined
Jun 13, 2014
Messages
48
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..... :)

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
 

GaryG9595

New Member
Joined
Jun 13, 2014
Messages
48

ADVERTISEMENT

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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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..... :)
You are welcome - thanks for the reply.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336

ADVERTISEMENT

@ GaryG9595.

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
...............
...........

… 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:

jptsunil

New Member
Joined
Jun 19, 2015
Messages
2
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:

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Re: Moving entire row to new sheet

. Hi jptsunil,

……..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"

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:

jptsunil

New Member
Joined
Jun 19, 2015
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,142
Messages
5,599,971
Members
414,353
Latest member
ljhan

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
Top