Free Microsoft Excel 2013 Quick Reference

Move rows to another tab when certain value.

I have searched on here, but still at a loss - seems to have to be done with a Macro, need for work and I am just not any good at this. Seems to be a simple thing.

I have the worksheet uploaded here:
http://www.4shared.com/file/2sUOCdmW...ssuesList.html

What I am looking to do is once an item on the OPEN tab, the status is changed to 'closed', it moves to the CLOSED tab. If on the closed tab, it changed to anything else but closed, it moves back automatically to the OPEN tab.

Possible that anyone can assist with this. I have tried to gather info from various threads but not really getting it all together.

Thank you for anyone that assist!

Sheila


Post your answer or comment

comments powered by Disqus
I'm in need of VBA to Copy&Paste / Cut&Paste rows to other worksheets when certain cell values are met.
I will include details of 3 similar macros I need.
Hopefully I will be able to adapt them myself for any additional similar macros or other projects in the future..
on sheet1:"LIST" 
	IF $D = "g", 
		Check $C value, 
			COPY & Paste $A:$L to first empty row of sheet2:"NEW" (repeat "$C" number of times)
				NEW!$A = LIST!$A, (VLOOKUP formula in NEW!$A using $L columns?) 
				NEW!$C = "", 
				NEW!$D = "s", 
					LIST!$C = "1", 
					LIST!$D = "e".
on sheet2:"NEW" 
	IF $D = "g", 
		CUT & Paste row to first empty row of sheet3:"ACTIVE", 
			"ACTIVE"$D = "", 
				DELETE row from "NEW" (without breaking formulas on sheet).
on sheet3:"ACTIVE" 
	IF $D = "Sold" OR "Sold*" OR "Paid" OR "Paid*", 
		CUT & Paste row to first empty row of sheet4:"SOLD", 
			DELETE row from "ACTIVE" (without breaking formulas on sheet).
Your help would be much appreciated!..

I've got a spreadsheet where I've created a button and when it's clicked it
will move the current row to the appropriate tab and insert it there instead
of replacing it. It also deletes the old row. Now, for my question
regarding this:

My spreadsheet is divided up into 3 sections. For the sake of simplicity,
I'll just call them Well Type 1, Well Type 2 and Well Type 3. They are laid
out like this:

WELL TYPE 1 INFORMATION
Smith #1 County ST 1/13/05
Jones #2 County ST 1/14/05

WELL TYPE 2 INFORMATION
Brown #3 County ST 1/16/05

WELL TYPE 3 INFORMATION
Haynes #6 County ST 1/17/05

Currently, under the last row of information under each section, there is an
add button that when clicked will add a new row (formatted as necessary with
borders and background cell color) under the specified section. My workbook
has 2 tabs, Western, and Tracking. Both of these tabs look exactly the same
(just like shown above), the only difference is the data that exists in each
section.

My question is this: now that I've got it set up to move the rows and
delete the old row, and it's working fine, how do I make excel move it to the
right section? If they click on a cell that falls under Well Type 2, and hit
the Move Row to Tracking Tab button, how can I make excel know to move it
under the Well Type 2 section of the Tracking Tab? How can it look for a
section title and know to insert it under that section? Also, if it inserts
the row, will it keep the appropriate formatting???

I'm not all that great with vba, but am learning quickly. Any help is
greatly appreciated!

Stacie

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
ActiveSheet.Range("A5").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Thanks!
Stacie

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
ActiveSheet.Range("A5").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

I need to make sure that the new row being moved over does not replace the
current row, but just moves it all down a row, or something similar so that
all the new stuff is there. I hope someone can help me... I'm desperate...

Thanks!
Stacie

I want to move a row to a different tab automatically when a cell has a date in it.

Example:
Three columns: Task, Date Due, Date Completed

When someone enters any date into the "Date Completed" column, the whole row moves to a tab named "Completed"

Any ideas?

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

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

I was trying to base on the solution found on this post:

http://www.excelforum.com/excel-prog...ell-value.html.

Trying to accomplish the same thing except I want it to be based on the status of the row.
I've attached an example workbook; in the example one can see there is information starting on the 3rd row and going from column A to I. Whenever a cell in column K is marked as Shipped, I want the row to move from the "Open Orders" sheet to the "Shipped" sheet; which is in the same format. Then have the row deleted from the "Open Orders" sheet.

I dont know how to make this conditional based as opposed to triggering via a button. If anyone could help me out I would greatly appreciate it. Thank you!

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

Can part of a row be automatically copied to another sheet when a specific entry is made in a specific cell?

For example:

1....................A..........................B.....................C
2................Name............... Number............Shift
3
4................Able....................101..................G1
5................Baker..................102..................G2

Can A4:B4 be automatically copied to Sheet Two when "G1" is entered into C4?

Thanks,

Sarah

I want to be able to take a row if possible when a date is filled and have it copy that same row the date is in to another sheet. When another date is added i would then like it to be removed from the second sheet and kept on the first sheet only. Below i have posted a sample picture of my data.

RA list.JPG

Here is just a little over view of what i would like to happen each time i open and update the excel sheet

insert date into Sheet "RA" "Column I" ---> Then have it copy the entire row onto sheet "RA in House" to the first available row--->when a date is filled in on Sheet "RA" "column J" it is then removed from sheet "RA in House."

If this is at all possible i would love for someone to help me out on this. If it isn't i would at least like the first part to be done up till the removal of the row when column J is filled. I could careless about removing the data when the column J date is added

I've created a button at the top of a spreadsheet that says "Move Row to
Tracking Tab". When this button is clicked, I want it to move the current
row (where their cursor is), to the Tracking Tab, and add it either at the
top of the spreadsheet (under the header row) or to the very bottom, while at
the same time, it deletes the old row from the first sheet entirely. The
first cell that contains data on the Tracking Tab is A4. Everything above
that is the header. (And just in case you need to know, the first tab is
titled "Western", and they are the only 2 tabs in the workbook) I've tried
the following code, but cant get it to work out right. It's giving me fits
with the line "Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

I am looking for vba code to move data from one spreadsheet tab to another. I need it to copy based on greater than or equal to and less than or equal to a certain column. I saw other related threads with just specific values; I need a range of numbers.

Attached is an example of the before with the data download, and the after when I just manually move them over to each tab. The tabs noted GT100K or GT50K means in column I values over 100,000 or 50,000, etc.

So the code would pull based on column C or C & I from the data tab, and copy to the other tabs. After moved If you could sort the tabs by B&C too, that would be perfect. If this just pulls from the top row down, then you can ignore the prior sentence since it will be sorted before running the code.

I'm not sure if my files are working so here is kind of an brief example. If you can give me the base, maybe I could manipulate the vba for my specifics.

Example :
Account Var. Amount
45000 $105
45005 $50
56100 $25
79008 $125
67002 $25

The code would move the data (first 2 rows) in one tab for accounts 45000-56009. Then move to another tab for accounts 45000-56009 and variance amounts greater than $100 (just the first row). Move data from accounts 56000-56999 to a separate tab (copy the third row into new tab). Etc.

Let me know if you have any questions.
Thanks,
Preston

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,

I have a workbook where I keep my tasks.
When a task is completed I want the row wher it is in to be placed to another sheet(Archief)

When in column F the value is changed to 'FIN' the row(A5 till K5 in the file I attached) has to be placed to the sheet 'Archief' below the last completed task.

Can anybody help me?

Thanks,
govi

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

OK. Need to tweak what I have below.

1-Need to move the selected cells not just copy from Retrieve tab to Master tab.
2-Need it to not clear out row 14 on Retrieve tab after pressing the Copy to Master button.
3-When moving I need the formats on the Retrieve and Master tabs to not change. So don't want vertical lines getting deleted etc....
4-After moving the data from Retrieve to Master, still need the Unique ID to remain on Retrieve tab. All Cells to right will be blank after the move.
5-Need button on Master tab that would move selected rows back to the Retrieve tab if they choose that row.
6-Thus will need a column A repeated on the Master tab too??
7-When rows moved back to Retrieve tab, they should be put back in their correct Unique ID row.
8-Add a 'Clear All' button on Master tab to clear everything moved over and move all rows back to Retrieve tab.

I attached file so you can see what I got going on.


	VB:
	
 
 
 
Sub CopyFromRetrieval() 
    Dim rngData As range 
    Dim rngResult As range 
    Dim rngHeaders As range 
    Dim NoCols As Long 
    Dim rngDst As range 
    Dim rngCrit As range 
    Dim LastRow As Long 
     
     
    application.ScreenUpdating = False 
    With Worksheets("Retrieval") 
         
         
        LastRow = .range("B" & Rows.Count).End(xlUp).Row 
         
         
        Set rngData = .range("A14:U1" & LastRow) 
         
         
    End With 
     
     
    Set rngHeaders = rngData.Rows(1) 
     
     
    NoCols = rngData.Columns.Count 
     
     
    rngHeaders.Cells(1, 1).Value = "Field1" 
     
     
    rngData.Cells(1, 1).AutoFill rngHeaders.Rows(1), xlFillDefault 
     
     
    With Worksheets("Master") 
         
         
        LastRow = .range("B" & Rows.Count).End(xlUp).Row 
         
         
        .range("B14:U" & LastRow).ClearContents 
         
         
        Set rngDst = .range("A14") 
         
         
    End With 
     
     
    Set rngCrit = Worksheets("Sheet3").range("A1:A2") 
     
     
    rngCrit.Value = application.Transpose(Array("Field1", "Copy")) 
     
     
    rngData.AdvancedFilter xlFilterCopy, rngCrit, rngCrit.Cells(1, 1).Offset(, 2), True 
     
     
    rngHeaders.ClearContents 
     
     
    rngCrit.Resize(, 2).EntireColumn.Delete 
     
     
    LastRow = Worksheets("Sheet3").range("A" & Rows.Count).End(xlUp).Row 
     
     
    Set rngResult = Worksheets("Sheet3").range("A2:U" & LastRow) 
     
     
    rngResult.Copy 
     
     
    rngDst.PasteSpecial xlPasteValues 
     
     
    rngResult.EntireColumn.Clear 
     
     
    rngDst.EntireColumn.Clear 
     
     
     
    application.ScreenUpdating = True 
     
    application.CutCopyMode = False 
     
     
End Sub 
 
 
Sub AllToCopy() 
     
     
     
     
    Worksheets("Retrieval").range("A15:A" & Worksheets("Retrieval").range("B" & Rows.Count).End(xlUp).Row).Value = "Copy" 
     
     
End Sub 
 
 
Sub AllToKeep() 
     
     
    Worksheets("Retrieval").range("A15:A" & Worksheets("Retrieval").range("B" & Rows.Count).End(xlUp).Row).Value = "Don't
Copy" 
    End Su 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
b

Hello All, hoping you can help with a problem I am having. I am very new to VB and macros so please bear with me as I try to explain. I found this code online and tweaked it for what I need. But I need to expand on it further and don't know how to add to the necessary values in the code.

I have a task list in excel, the entries are made in rows, and I am using the following code on Sheet1 to copy row entries in which a Cell (always C, C4, C5, C6, ect...) is marked "Completed", to the next available row on Sheet3 in the same workbook, and then delete that copied row from Sheet1.


	VB:
	
 Range) 
     
    Dim nextrow As Long, i As Long 
     
    nextrow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1 
     
    If Target.Cells.Count > 1 Then Exit Sub 
     
    Application.ScreenUpdating = False 
     
    If Not Intersect(Target, Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)) Is Nothing Then 
        i = Target.Row 
         
        If Target.Value = "Completed" Then 
            Range(Cells(i, "A"), Cells(i, "J")).Copy Sheet3.Range("A" & nextrow) 
            Range("A" & Target.Row).EntireRow.Delete 
        End If 
         
    End If 
     
    Application.CutCopyMode = False 
    Application.ScreenUpdating = True 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I need is to make it so that when the same cell is marked "Completed with Compromise" it does the same thing as above, but moves it to Sheet2. I tired adding if statements in there, and I can't get it to work. I know I will need to specify another "nextrow =" and make it Sheet2, and if I insert another "if statement in there it will work somewhat, but I get a runtime error 1004. And if I click "End" it will actually work, but this is obviously not as fluid as it needs to be.

Any help is greatly appreciated, excel and vb is new to me, and I love a challenge, but I've searched and experimented for hours with this and can't get it working.

Thanks

Ok so from the looks of the forums this type of question gets asked alot. I have a Daily Report that I run each day. There are several Failure Codes that need to be move to another sheet. Normally I cut and paste all the data from one sheet to another. So my question is, how to move a row from one sheet to another based on specific data in a cell? I do not need the row removed after it is copied to the destination sheet. That said, I have looked at alot of the answers on here but because my macro knowledge is limited I can't seem to get anything to work. Please take a look at the workbook below and see if you can help.

Workbook here. The file is quite large so I hosted it somewhere else.

I basically need each Failure Code moved to the tab that matches its name. So all labor to the labor tab and so on. Another thing that would be great but is not necessary -certain Failure Codes can go on more than one sheet, if the Failure Code meets a second criteria can it be moved to a different sheet? Example, Failure Code at a certain Location.

A few things to know. ELEV and PEST go to the Subcon tab. PAINT and ROOF go on the Carpenter tab along with all the CARP.

One last thing, if you look at column G, I am using an IF formula to add to the dates in column F. I would like to know if there is a way to add to the IF function to make it only run if there is data in column F.

Thanks for taking a look.

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.

Hi

I wanted to know how to double click any cell in a row so that it moves the whole row (or just a A-M if possible) to to the first free row in another another tab.

any help would be appreciated

When I enter an amount into the A column, move a group of cells in that row
to another sheet in Excel.

Hi guys,

I am sure its a piece of cake for experts. I am not progammer so for me its difficult.

I would like to have a MACRO to search for bunch of keywords and the file which can have various number of rows.

Something like that. I will top this set of keywords every now and then.
The macro should perform a search like filter and ideally move all rows which have any particular keyword to new tab or if
its not possible I can do it manually, no probs. 

I couldn't find anything ready made on forums here because I can't really formulate the search correctly. Can anyone help in creation of this macro, please!

Thanks


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