Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

move rows to another worksheet with vba

I need help to find a value or name in a column and move all rows containing that value to another worksheet. It will be good if there's an input box to key in the value to search. I have attached my excel file & in Open worksheet the value or rather the name to find is in column D titled Person. I would like to find all rows with say John in Col D and move it to the next blank row ie row7 in Closed worksheet.


Post your answer or comment

comments powered by Disqus
I need help with a macro to move rows to different worksheets. For example, If I put in Delivered, I want the row moved to a "Delivered" worksheet. If I put in Declined, I want the row moved to a "Declined" worksheet. I have been able to successfully get one of these to work at a time, but I need both of these to work in a single Macro. Help anyone? Here's the code I used to move one of them.

Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

If Flag = True Then Exit Sub
If Not Intersect(Target, Range("B2:B100" & LR)) Is Nothing Then
If Target.Value = "Delivered" Then

LR = Sheets("DELIVERED").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy
Sheets("DELIVERED").Range("A" & LR).PasteSpecial
Flag = True
Target.EntireRow.Delete
End If
End If
Application.CutCopyMode = False
Flag = False
End Sub

/close

Hello again guys/girls, i am in need of assistance...again.
I have mulitple spreadsheets, with pretty much the same data layout. In Column A, i have product code, Column B i have product name, and various other stuff in columns C,D,E,F,G ect. I'm looking for a macro, or a way to move certain rows to another worksheet. What i've done is, in the rows i need moving, i have put a "1" next to it in its own column. So in one worksheet i have column E full of 1's, and another worksheet i have column D full of 1's. I need a way for the macro to ask me what column to look for the 1's in. If it finds a 1 in the column i have specified, it will move the whole row to "sheet2". I think i've explained that correctly, if you need any other information, i'll post it up. Thanks in advance
Steve

Hi, I'm new to VBA code but have managed to find the following code to move row information from 1 sheet to another sheet in the same workbook based on selecting "yes" from a validation list in column F. The problem I am having is when each row is moved to sheet2 it replaces the previously moved row. I need the rows to be added to the next available row on sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
Application.EnableEvents = False
If LCase(Target) = "yes" Then
With Sheets("sheet2")
lr = .Cells(Rows.Count, 5).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=.Cells(lr, 1)
Target.EntireRow.Delete
End With
End If
Application.EnableEvents = True
End Sub


Hello,

I currently have a consolidated worksheet (thanks Bill!) called "Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up?

Edit: here are some additional information:

The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)

The "status" column is at column 11.

Thanks for your help in advance,
Henry

Hi. I have a worksheet called "ALL DATA" that contains a list of methods that are marked by a validated column that can only contain "Lift, Evacuate, Assist or Wheels."

Is there a way to move the rows with a value of "Evacuate" to another worksheet called "TRAINING REQUIRED"? Also when this move is done, that row is still required in ALL DATA and should remain. So I guess its like copying a list of names who are evacuators to another worksheet in preparation for training the named people.

It would be brilliant if i could also prevent names from being duplicated in the TRAINING REQUIRED worksheet.

Thanks for your help in advance

I need help to find a value or name in a column and move all rows containing that value to another worksheet. It will be good if there's an input box to key in the value to search. In my excel file in Open worksheet the value or rather the name to be search is in column D titled Person. I would like to find all rows with say John in Col D and move it to the next blank row ie row7 in Closed worksheet.

Hey, I'm curious if it is possible to move a Range of cells to another worksheet if a value in one of the cells in the Range is equal to a certain value?

For example, if Row 1 in excel has 5 columns and the 4th column is equal to 2 (integer), it would move the entire row to another worksheet, removing it from the current worksheet as well. Is this a possibility?

Thanks,
n3m.

Hello all,

I had a question about excel macros and VBA. So I have a sheet of raw data within an excel workbook. I already created a macro to create new worksheets. What I want to do is iterate through the raw data and depending on a value in a certain column, I want to copy that row to another worksheet. I think I know the beginning and the end, but I cant figure out how to iterate through the rows.

Any help would be much apprecieated.

Thanks!

Hi guys,

I am wondering if someone could help with this..

I have a VBA which moves a row from the outstanding work sheet to a completed work sheet when the status in column J is changed to 'Complete'. In column A is the Job name which I have set as a hyperlink to a individual folder for that project.

My problem is that when the job is complete and it moves to another worksheet, it does not carry the hyperlink with it.

Anybody know of a way to do this??

I have attached the file for reference.

Thanks

Dan

Hi..I have a little problem and could use some help please.I created a worksheet for logging orders but now find it too large to cut and paste but have little knowledge of macros

I would like to have any completed rows moved to another sheet in the same workbook

eg

file...........model............. colour............. order date........... days old .................filled

xx ________tree__________ green_________08/18/06 ___________4 _____________ 08/22/06
zz________ bush__________brown_________08/19/06____________3 _____________08/22/06
aa________ snake________ brown_________ 08/21/06____________1

I need help with moving any rows where the "filled" date column has been filled in manually to another worksheet ,moving the unfilled rows up

Any help would be greatly appreciated

thanks

Hi! I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.

Is this even possible???

As always, thanks! You all are so awesome!!!

Sorry if the title is confusing. Essentially I have VBA code that currently copies the selected rows to another worksheet below the last entry.

Sub Macro1()
Selection.EntireRow.Copy
With Sheets("Storage")
.Rows(.Range("A" & .Rows.Count) _
.End(xlUp)(2).Row) _
.Insert Shift:=xlDown
End With
End Sub

This works great, but I would like the row to now be copied to the Storage worksheet but starting in column C. In essence, column A of the original worksheet will be copied to column C of the Storage worksheet, column B to column D, etc. I cannot seem to alter the code so that the selected rows are copied to the Storage worksheet but beginning in column C instead of A.

Any help would be greatly appreciated. Thanks.

Help!
I'm trying to create a macro to cut a row from one worksheet and move it to an archive work sheet.
I've used macro record to cut and past but when I cut and past another row, it wipes out the first one I archived. This is what I have so far

Sub archive()
'
' archive Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Rows("2:2").Select
Selection.Cut
Sheets("Archive").Select
Rows("2:2").Select
ActiveSheet.Paste
Rows("3:3").Select
Sheets("work orders").Select
Selection.Delete Shift:=xlUp
End Sub

I have a spreadsheet with about 800 rows. In column D, there is a variety of numbers from 013 to 080. There could be anywhere from 2 to 100 rows to each number. Everytime the number changes in column D, I would like to copy that group of rows to either another tab or to another worksheet. So, Rows 1-5 have 013 and rows 6-20 have 018 in column D and rows 21-81, have 025 in column D. Is is possible to have it moves rows 6-20 to one worksheet and rows 21-81 to another worksheet and keep going until there is no more data?

Hi Excel experts,
I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.

Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.

Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.

Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.

Can anyone assist?

Thanks!

Hi there,
I have read through related posts though am still struggling.
I am wanting to move the row from one worksheet "Open orders" to another worksheet "Completed orders" when order becomes complete.
This will be an ongoing function that will be updates daily.

Please see what I have below:


	VB:
	
 OrderTracking() 
    Sheets("Open orders").Select 
    Selection.AutoFilter Field:=1, Criteria1:="Complete" 
    Range("A3").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Copy 
    Sheets("Completed Orders").Select 
    Range("A1").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    ActiveSheet.Paste 
    Sheets("Open orders").Select 
    Application.CutCopyMode = False 
    Selection.EntireRow.Delete 
    Selection.AutoFilter Field:=1 
    ActiveCell.Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance.
Cheers,
Simon

Dear All,

I am a new user to this forum, but have read many great solutions and I am pretty sure there are people in this forum that can help me.

I have a excel file to keep track of tasks or actions that need to be performed. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows (so an entire task) of completed tasks to another sheet called, "Completed Actions". In Actions sheet I have a column for " status" and here you have to select from a drop down menu, either "On-going", "Urgent" or "Done". What I would like, is that once you have selected "Done", the entire row or entry, will be automatically moved to the "Completed Actions" sheet.

Look forward to reading some answers and/or comments.

Carsten

Can anyone give me an easy way to move around my worksheet with VBA. For exampla if I want to move one or more cell up/down - right/left from the active cell?

hi all

Just started to have a look at vba as I am trying to use excel more efficiently, the problem I have is this, my spreadshet consists of rows like the following

DATE NAME FACULTY REASON LESSONS
8/7 MR JONES ENGLISH ILLNESS 6
8/7 MRS MEL MATHS LOA 5
9/7 MR TOM SCIENCE MEETING 3

I want to copy the rows to other worksheets dependent on column C (faculty), so someone from english would go onto a worksheet for english,a row with maths would go into a worksheet called maths etc.

thank you in advance of any advice

I thank Nigel for helping with the code to copy cells to another
worksheet when a date is added. I tried doing another code to delete
the copy if the date is deleted, but I am new and not sure what all the
codes mean yet or where to put the false statement or if this is
possible. Everything I tried does not work. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'from Google Group microsoft.public.excel.programming
If Target.Column = 4 And Target.Row > 1 Then
If IsDate(Target.Value) And Cells(Target.Row, 30) <> 1 Then
'transfer parts of this row to Record sheet or delete row if
column date is deleted
With Sheets("Record")
' <<< change the name of the target sheet here

'determine last row on record sheet
Dim xlr As Long
xlr = .Cells(.Rows.Count, 1).End(xlUp).Row

'copy selected cells to record from production (change for
other columns ect,)
.Cells(xlr + 1, 1) = Cells(Target.Row, "D")
.Cells(xlr + 1, 2) = Cells(Target.Row, "F")
.Cells(xlr + 1, 3) = Cells(Target.Row, "H")
.Cells(xlr + 1, 4) = Cells(Target.Row, "N")
.Cells(xlr + 1, 5) = Cells(Target.Row, "T")
.Cells(xlr + 1, 6) = Cells(Target.Row, "U")

'record that row data has been transfered
Cells(Target.Row, 30) = 1

'sort the record sheet by PC
.Range("A2:F" & xlr + 1).Sort Key1:=.Range("D2"),
Order1:=xlAscending

End With
End If
End If
End Sub

Thank you for any help,
Peaches

Hi all,

Is it possible using VB to move rows which contain a certain value in one of the columns.

For example, if column G contained success, that row would be moved to the success worksheet.

Any help greatly appreciated.

Thanks

Im new here as you can tell! I have whats going to be a very large "Input" worksheet that has several headings of columns. One of these columns is "category" as in parts of a vehicle, i.e. steering, wheels, rod-ends and so on. Now what I would like to do, is once I type a new row of information in, to have the filter go through and copy said category row into another worksheet where I can have all of them separated on there own.

Here is a picture of my current spreadsheet. So basically I want to be able once I save the file, that it goes through and where it detects Rear-End in "B18" I would like it to select row 18, copy all of its cells and move them into the worksheet Rear-End

Thanks Alot, Matt

http://img.photobucket.com/albums/v1...8svt/prtsc.jpg

Hi Everyone, I am new here. I have scoured the internet looking for a code that will work for my situation. I have copied macros code and tried to adapt it to what I need, but nothing seems to work for me.
I have a work sheet, and for each entry that is marked "Closed: By SEG POC" or "Closed: Referred" , I would like the entire row to move to the Closed Log worksheet.
Once that row is removed, I want that now vacant row to delete and move up so there are no blank spaces in either worksheet. Thanks to anyone who can help me out!!!
TEST_Inquiry_Log.xls

Hi all.

Say that Filtered the table (Autofilter) using a criteria from HEADER B "Dog" and from HEADER C "Sunday". I then get this filtered table: (Sheet 1)

HEADER A | HEADER B | HEADER C | HEADER D | HEADER E
---------------------------------------------------------
A3 | Dog | C3 | Sunday | E3
A5 | Dog | C5 | Sunday | E5
A6 | Dog | C6 | Sunday | E6

Criteria is picked thru a combobox via userform. So each combobox have a variable fe:


	VB:
	
 
    .AutoFilter Field:=3, Criteria1:=Me.cmbHeaderB.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I copy data to another worksheet (Sheet 2) so that it will look as below? (The column with the filtered data will not be included, but is written on the top of the table, assume C1)

Header B: Dog
Header D: Sunday

HEADER A | HEADER C | HEADER E
----------------------------------
A3 | C3 | E3
A5 | C5 | E5
A6 | C6 | E6

Thanks a lot


No luck finding an answer? You could always try Google.