Move a range of cells to another sheet

[bowhunt24]

Hello,

I've done a bunch of searching around and it looks like a lot of folks have a similar questions about this however I haven't seen anyone ask exactly what i'm after...hoping someone can help.

I have within one workbook two worksheets...It is basically a sales tracker and when I mark a row "CLOSED" using a drop down list I want that range of cells say C5:O5 to either copy or move (don't really care either way) to the other worksheet called "Closed Business". On top of that, I would love for it to move or copy that info to the first open space as there may already be data in that sheet and I don't want it to copy over it.

Also, I don't necessarily want to just move the whole row because there are columns that contain data i don't want moved over...just the range I mentioned above as an example.

Hope this makes sense! I can reply back with additional details or the actual file if needed! Thanks for the help everyone!

Evan

[Leith Ross]

Hello Evan,

Welcome to the Forum!

Creating a solution would be faster and more accurate if I had the workbook to check my work against. Can you post a copy of the workbook?

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[bowhunt24]

Hi Leith, thank you so much or the quick help! I added the file to my original post. Let me know if you need any additional details....

Again...THANK YOU! I'm usually pretty good at figuring this stuff out or finding an answer in a forum, but this one has got me good!

Evan

[Leith Ross]

Hello Evan,

You are referring to the drop down in column "N" of "CLOSED BUSINESS_MTD SUMMARY", correct?

[bowhunt24]

Hello Evan,

You are referring to the drop down in column "N" of "CLOSED BUSINESS_MTD SUMMARY", correct?

on Sheet "OPEN LEADS", column "L" has a the same drop down. When you select a lead as "CLOSED" in that drop down I would like that info to be copied to "CLOSED BUSINESS_MTD SUMMARY".

The reason the closed business sheet also has drop downs is because you may enter in business that you closed that wasn't a lead to begin with. So for example, maybe you closed a few accounts that you never considered leads so there is some information on the "CLOSED BUSINESS..." sheet but then a few of your leads closed too, so you change their status to "CLOSED" and then it would copy that into the next available spot on the "CLOSED BUSINESS..." sheet.

Ahhh...I've never had to type out explaining this stuff so please excuse my ramblings

[Leith Ross]

Hello Evan,

First time for everything. Thanks for the explanation. It is clear to me now. I need to go vote (local issues) before the polls close and I'll be back in about an hour.

[therzakid]

Hello, please right click the tab titled "OPEN LEADS", select "view code", and paste the following:

Please Login or Register  to view this content.

Best,
Craig

[bowhunt24]

Hello, please right click the tab titled "OPEN LEADS", select "view code", and paste the following:

Please Login or Register  to view this content.

Best,
Craig

Craig, that is awesome! Thanks for that snippet of code, it seems to work perfectly! I was curious if I may be able to paste the same code on the other sheet so that if I change them from "CLOSED" back to "30", "60", "90" or "NA" they will go back to the other sheet. I can probably figure out how to swap all the sheets and ranging around, but how to I tell it to look for all of those values instead of just one value? For example right now, you have "If Target.Value = "CLOSED" Then" ...... can I put multiple criteria in there?

Thanks so much!

Evan

[bowhunt24]

Sweet! I got that code working in the reverse too now so you can move stuff back to the other sheet just in case. Just a couple quick things:
1) I only have it set to move back if you select "90" because I can't figure out how to tell it that multiple values can send it back, ie, 90, 60, 30 or N/A.
2) When you move data back and forth is creates a blank row and then if you move it back it goes to the end as opposed to the first blank row. Anyway to make it go into the first blank space instead of just to the bottom?

These last two things are just to clean it up, I don't imagine that I will be selecting business as "closed" and then need to move it back for any reason, but you never know

Thanks again!
Evan

[bowhunt24]

Okay, so I figured out how to make it look for multiple values....so I'm all set!

Now onto the last step....this sheet that I created will be used by individuals reporting up to one area. I have created a similar spreadsheet that can be used to consolidate all of the business from various stores into one master sheet. It would be really nice to create some kind of Macro or something to make it easy to import data from workbook to another...however I'm totally fine with Copy/Paste. The only problem is when I try to copy data from the workbook and paste it into the master workbook Excel totally locks up and goes not responding. Have to task manager and end the whole process.

So my question is...what is causing Excel to lock up every time I try to paste data into the master workbook. Is it the data validation or conditional formatting? Is there an easy way to create a macro that will move the selected data to the master?

Thanks again everyone! The help so far has been amazing on this forum!

Evan