Free Microsoft Excel 2013 Quick Reference

Move row to different tab based on derived value

So after researching it looks like I may have the wrong workbook event, but am unsure how to change the code to accomodate a different event.

Right now I have a 'Status' field in column H that is derived by comparing today's date to dates in columns E & F. The macro below is setup to move 'Completed' items to the 'Completed' sheet, and move all other items ('Planned', 'Unplanned','In Progress') to the 'Current State' sheet. Unfortunately, in order to trigger the macro I have to double click on the cell in column H and hit enter.

I'm actually wanting the macro to kickoff whenever the value in column H changes (even if it is the result of a change to column E or F. I'm thinking maybe I should have the WorksheetCalculate event, but as mentioned above.

I'd also like to autosort the data as the current code moves the active row to the bottom anytime the macro is triggered.

In advance, I very much appreciate the time.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range
        Set rng = Target.Parent.Range("H4:H500")
            '   Only look at single cell change
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met
            Select Case Target.Text
                Case "COMPLETED"
                    Target.EntireRow.Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(0)
                Case "UNPLANNED"
                    Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                Case "PLANNED"
                    Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                Case "IN PROGRESS"
                    Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
            End Select
End Sub
Thanks for the info


Post your answer or comment

comments powered by Disqus
Hi I am hoping someone can help me,

I have a "Course Builder" sheet which people enter course codes onto, i have the macro to move the course info to a "Total Library" sheet and clear the data from the course builder sheet. What i would like to do now is have a macro/code that searches the "Total Library" sheet and copys a row from column H:R to another sheet based on the value in column C. For example column C value is "NRDC_" this row moves to a sheet named "NRDC", i have 11 different sheets to move info into. I would need to paste it into the next available row but not copy a course code if it already exists on the target sheet.
I can do this with a macro by filtering the data on Total Library for all 11 sheets and copy and paste that way, i was just wondering if anyone new of a smarter/quicker way of doing it?

Any help would be gratefully received.

Irleand316

I would like to write a macro to loop through each row of a worksheet titled
'Data' and copy each row into seperate worksheets, based on the value in
column A.

Col A Col B Col C etc
Test1 ... ......
Test2 .... ......
Test3 ...... ......
Test2 ...... .......

Row 1 would copy into worksheet 'Test1'
Row 2 would copy into worksheet 'Test2'
Row 3 would copy into worksheet 'Test3'
Row 4 would copy into worksheet 'Test1'

The worksheets to copy into are known and are already set up.

Any help on this problem would be very gratefully received.

Thanks

hello,

I am a new member to this site and found it very useful. However I have been searching the forums and trying to learn how to edit these codes all weekend. I have a worksheet that needs to be sorted then moved to another sheet based on a value. I've tried serveral codes on this site but none seems to work. I have 200+ books that I need to sort and moved. The value is from column F. For every unique item I have to put that into a seperate sheet with like items.

attached is a sample. Its an autocad points ifle. I have to seperate each point based on what type of object it is. Once the sheet is created i have to save sheet as a comma delimited text so I can import it into autocad.

Here's what I'm trying to do. I have an excel document where each row represents an activity. One of the columns states what the status of that activity is, anything from 1 to 6 (column C on my document). What I want to do is make the document to where status 1 activities go to a "status 1" worksheet, etc.

Also, if an activity changes from status 2 to 3 I want the activity (the entire row) to automatically move to the "status 3" worksheet. I have very little knowledge of macros and how to incorporate them into an excel document. Any help anyone can give me would be greatly appreciated. The amount of rows is expected to increase greatly, which is why I'm not going to do a sort and cut/paste.

I appologize. I know versions of this question have been asked before, but I lack the technical capacity to apply that knowledge to my own situation.

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

Ok I need some help with getting a marco functioning on a spreadsheet that my office uses to input patient EOB data for record keeping. In the past data was entered manually but eventually the amount of data becomes overwhelming to manage.

Basically what I need is for a marco to run when you click the “Run Me To Sort” button on the Temp worksheet. At this time we need rows to be moved from the Temp worksheet to other worksheets based on what month was entered. The other works sheets are named by month.

So for example a row with

Ins1 123 PC 4/2/2012 21.89

Would be moved to the April worksheet because of the date 4/2/2012. New spreadsheets will be created for every year so error checking on the year is unnecessary.

Dates are always in the 4th column.

The marco needs to only run when the button is clicked. That way data can be manually reviewed on the Temp worksheet first.

Once the row has been moved to the appropriate worksheet the line can be removed from the Temp worksheet.

I have included an example spreadsheet setup just like the one we are going to use.

Thank you ahead of time for helping me with this, it will allow other employees other then myself to keep up with the data.

Hi

I have a list with a few hundred rows of data. I would like to write a macro that will copy each row into a different sheet based on the value in column A.

I'm having trouble writing a code that will go through each row and recognize the value in column A. Once I get there, I could use if statements to place it in the right row on each new page.

I was approaching it using the code below. However I don't know how to determine the row that the code is in while going through the loop. Maybe there's a different approach


	VB:
	
 Range 
 
For Each rRng In Worksheets("All").Range("A4:A" & _ 
    Worksheets("All").Range("A65536").End(xlUp).Row) 
     
    If ("Value in column A of that row = Value1") Then 
        copy To Worksheets("Value1") 
    ElseIf ("Value in column A of that row = Value2") Then 
        copy To Worksheets("Value2") 
    End If 
     
     
Next rRng 

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

Thanks

So, i can't figure out how to copy an entire row to a different page based on the entry in one cell.

Like, if the cell value is 2 the row goes to sheet 2, if the cell value is 3 the row goes to sheet 3. I'de really like to use words and a drop down menu though... but think that will be considerably more confusing.

It's for a number of logs. ie sheet 1 is the master log, and the other sheets are just status tracking logs.

thank you,
srh5454

Hi, I am attaching herewith data sheet.

I want to transfer single column data to different columns based on their headings which is same as written in that one single column.

Can anyone please try to solve this query as my data is very huge and typing manually can cause error also.

Macro to allocated invoice amt to different months based on the date range of the invoice (contract period).

Need help in developing a macro that will colour the sheet tab based on
the sheet name or cell value in a sheet that is equal to, for example,
saturday or sunday. For example, Oct 2, 2005 - SUN. I have 31 sheets
for each day of the month, named 1, 2, 3, etc... Having the sheets
colour coded just makes it visually easier to select the proper sheet.

Any suggestions would be appreciated.

Thanks you.

Terry

i, I am attaching herewith data sheet.

I want to transfer single column data to different columns based on their headings which is same as written in that one single column.

Can anyone please try to solve this query as my data is very huge and typing manually can cause error also

I have a spreadsheet that receives constant updates and I need a formula to copy a complete row to another spreadsheet based on a single condition:
A B C D E F G
TEAM
136 2/24/2010 Carlos Miro XOXO 138 Paid online
119 Lisa Lamora XOXO1 128 599
103 Brittany Lumpkin XOXO3 128 188
136 Carlos Miro XOXO1 1289
136 Amaury Alvarez XOXO111
103 Carlos Miro XOXO112
103 Candace Sowdon XOXOXO2 Paid online
119 Mauricio Gomez XOXOXO 1209 Paid online

(Please see attachment for details)

Based on the input of this table, I want to be able to extract the rows that belong to team 103 (for example) and copy them into another table (for that team alone).

I don't want to use Advance filter, but would like to use a formula. I've tried vlookup, but it would these arenot unique rows, I will only get the first line.

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

Hi,

I need a big help from this group.i am newbie to VBA.I copied this code from another file & changed accordingly to my needs.But did not get success,

I got a “Main” sheet in excel & want to get them copied to SID sheet based on the value selected in col E.

Suppose if I input SID in col E,then entire row wherever SID is reflecting in Col C in Main sheet should get copied to SID sheet.

I am attaching the zipped file.

Regards
Raj

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
increments.
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?

All,

I have a workbook that has 100 worksheets in it, i want the worksheets to be renamed based on the value in cell b1 for each worksheet. However if there are invalid characters or the name is too long it gives an error. How I just reference the first 30 valid characters.

Thanks again.

Option Explicit

Sub LoopThroughSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ws.Name = ws.Range("b1").Value

Next ws
End Sub

Hi

I have a spread sheet that has been soreted by client Key in ascending order.

Currently, anyone who is a director to the client key is in a single column.
For example, I may have 50 different client keys but because there are 3
directors for each key column A (CLient Key) will show 3 of each client key
50 times. See below for quick example.

Client Key Full CLient Name Director
---------------------------------------------------------
AAA Appy Arry Director 1
AAA Appy Arry Director 2
AAA Appy Arry Director 3
BBB Bobs Beer Bike Director 1
BBB Bobs Beer Bike Director 2
BBB Bobs Beer Bike Director 3
CCC Chick Chick Chick Director 1
CCC Chick Chick Chick Director 1
CCC Chick Chick Chick Director 1

What I would like to happen is to be able to run a macro that will look for
the client key in column A and remove the duplicated rows of information but
place the Director 2 and Director 3 into there own seperate colum as we will
need this information. See below for how it should then look.

Client Key Full CLient Name Director Director 2
Director 3
---------------------------------------------------------------------------------------------
AAA Appy Arry Director 1 Director 2
Director 3
BBB Bobs Beer Bike Director 1 Director 2
Director 3
CCC Chick Chick Chick Director 1 Director 2
Director 3

Please could you give as much information as possible as to how to achieve
this as I am a complete novice at this stuff.

Also, I have seperate worksheets that will be running a similar routine but
each worksheet was created based on how many directors are for each client,
so in this example there are 3 directors for every client but other
worksheets will have 4 or even 5 directors. Please try and explain the code
so I can then copy the information into the other sheets and not have to
re-write completely in order to run for the other sheets.

Thanks in advance

Malcolm

I'm new to this forum and new to excel, could anyone help me figure out how to move rows into sheets based on cell data.
example; I have a row of information and I want to move that row to a specific sheet based on the status of column R or E whichever it may be?
Thank you very much!

Hi..

I could use some help to speed up my worksheet..I have created a worksheet
to help me with orders and when they get filled. I have 7 columns with the
item ,order date,cost etc and am looking to move " filled orders" to another
sheet within the same workbook based on the date received column eg( as soon
as date is entered in date received column row is moved).Is this possible?
Can it be set up so the remaining rows move up on the first worksheet?

thanks in advance

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!

Hello,

I have response form from an online survey. There are 200 potential answers. I am trying to find an easier way to filter out the people who didn't answer any of the questions. Basically the survey returns N/A if no answer is given. So my thoughts were to scan each cell in a row for something other than N/A. If any of the 200 cells return something other than N/A move on to the the next row. If only N/A's are found relocate that row to a new summary worksheet.

I hope that explains it properly. If not I will be glad to answer any questions.

Thanks in advance.

TKO

I searched and found many relevant posts, but they all seem specific to the worksheet at hand and I do not know enough with excel to transform the script to work with what I need it to do.

Basically I have a workbook with 2 sheets: To Be Done, and Completed. All jobs are put in the 'To Be Done' sheet. Once they have been completed, I would like excel to automatically move them to the 'Completed' sheet; same style, design, everything.

I have attached the workbook I am working on. If there is information in the "notes, followup, resolution" column, that job is considered completed and i would like to have it moved (the entire row) to the 'Completed' sheet AND have it taken out of the 'To Be Done' sheet.

thanks in advance!

hotlist1.xls

I have a workbook that has 4 sheets. What I would like to do is that every time the word "completed" is entered in column, the entire row will be automatically removed and moved to sheet titled "Completed." What I have been doing now is cutting and pasting the rows manually and I want to avoid doing that.

Here is some information....
I have a sheet called "Appeals-Updated" that has columns from A thru M....so when I enter "completed" on column M the entire row with entered information will be moved.


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