?
Solved

Macro in Excel to move rows to another tab

Posted on 2011-02-25
7
Medium Priority
?
286 Views
Last Modified: 2012-05-11
Hi All,

I want to create a drop down menu in a field with reference values, and, depnding on the value selected, move the row to a different tab in the workbook.

Is this possible?  How would you suggest accomplishing?

Thanks,
Matt
0
Comment
Question by:mattturley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34985964
Matt, Please provide a sample sheet so that I can get my references correct.

Sid
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 34985966
We might be able to help you if you provide a sample file.
0
 

Author Comment

by:mattturley
ID: 35147970
Attached is a sample file.  Goal is to move rows when a statu is selected.  Status and tabs are named the same. demo-ai-list.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 35151888
Try this macro. You will have to complete the status assignments then run the macro.
Private Sub moverows()
Set stcol = ActiveSheet.Rows(1).Find("Status")
sr = ActiveSheet.Range("A65535").End(xlUp).Row
i = 2
Do
    Set cel = Cells(i, stcol.Column)
    If cel.Value <> ActiveSheet.Name Then
        cel.EntireRow.Copy Sheets(cel.Value).Rows(Sheets(cel.Value).Range("A65536").End(xlUp).Offset(1, 0).Row)
        cel.EntireRow.Delete
        i = i - 1
        sr = sr - 1

    End If
    i = i + 1
Loop While i <= sr
End Sub

Open in new window


I had considered moving the row as soon as the status was selected but the decided against it as it may cause problems, (eg if someone selects a wrong value it would have moved the row immediately to the selected sheet and will have to be moved back causing too much nuisance).

Saqib
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35162859
Matt: Could you please confirm if the above worked for you as I had also worked on the above and I have the sample file ready. My code using a different logic though.

Sid
0
 
LVL 50
ID: 35744597
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question