Problem with macro that resets and erases new data each time it is run.

[SMcuthbert]

Greetings:

The attached workbook shows a terrific macro (module 2) a user created for me that allows me to take specific cells from a row with a check box in column "t" ticked and copied to the sheet labeled "programs requested". But I just discovered one minor glitch: I have new columns in "Programs Requested" that I want to update as I go for each of the records copied over. Yet each time I run the macro, rather than adding new rows in the next available space on Programs Requested, it resets (clears and re-copies). Is there any way to rework this macro so that new rows are added each time I run the macro, rather than resetting? I think that will allow me to continuously update "Programs Requested" without it erasing and resetting info each time. I hope I explained this well.

Thanks very much!

[jaslake]

Hi SMcuthbert

Try this

Please Login or Register  to view this content.

[SMcuthbert]

Hi. I request one minor adjustment....now each time I run the macro it adds previously added rows again to the "program requested" spreadsheet. I would only like newly checked rows from the other spreadsheet added each time the macro is run, so that it a running list with no duplicates. Is that possible? Thanks very much.

Sue

[jaslake]

Hi Sue

The line of Code indicated below will clear the CheckBox after processing, so, you won't get duplicates UNLESS you select the same item again.

Add the line of Code as indicated...let me know of issues.

Please Login or Register  to view this content.

[SMcuthbert]

Thank you. This helps. But does this mean that I have to create another check box to show me on each spreadsheet if I've sent a certain record over to "programs requested"? Is there anyway to keep that indicator there while still not duplicating? Thanks so much for your help on this.

Sue

[jaslake]

Hi Sue

There's a couple of ways this can be dealt with.

Approach 1. Do a "Find" for the "Program Requested" in Sheet Program Requested for each Check Box item on all Worksheets. I'd need to know which matching elements are the identifiers. It appears to be Columns B and R on the Individual Sheets compared to Columns B and I on Sheet Program Requested. Is this true?

This approach may take a while to run...or not...don't know.

Approach 2. After each Check Box on the Individual Sheet is processed place a marker on that Check Box line to indicate it's been transferred. I'd place an "X" or some such thing in "some out of range" Column...say Column Z. The Code would then look at all Check Box Rows that are "Checked". If there's no "X" in Column Z of that Row, the Record gets processed. If there is an "X" the Record does not get processed.

The second approach would be, in my opinion, more efficient. Let me know your thoughts. If you should decide approach #2 tell me what Column I can have.

[SMcuthbert]

Hi!

Let's go with option 2. I agree that is more efficient. Can we use column "U"? It's right next to T, so it will be easy for me to check box T and then put an X in box U. How does that sound? Thank you again!

[jaslake]

Hi Sue

I've changed direction on you a bit. This Code in the attached moves the Data when you check a Check Box and then disables the Check Box so it can't be selected again.

Please Login or Register  to view this content.

Try it...let me know of issues.

[SMcuthbert]

I love that the data is copied automatically when I check the box and I don't have to run the macro each time. It is almost perfect. But what if I accidentally check a box incorrectly or want to undo a box that I've checked? Is there a way to do that? Or would I just need to erase the copied row from the "programs requested" spread sheet if I don't want it there? Minor thing, really. But I thought I'd ask.

Thank you again. This has been a real learning experience for me!
Sue

[SMcuthbert]

I love that the data is copied automatically when I check the box and I don't have to run the macro each time. It is almost perfect. But what if I accidentally check a box incorrectly or want to undo a box that I've checked? Is there a way to do that? Or would I just need to erase the copied row from the "programs requested" spread sheet if I don't want it there? Minor thing, really. But I thought I'd ask.

Thank you again. This has been a real learning experience for me!
Sue

[jaslake]

Hi Sue

Are you the USER using this File?

[SMcuthbert]

I'm not sure I understand your question - but yes, this is my file and I am the one who uses it.

[jaslake]

Hi Sue

Well, it makes a difference in that you don't need to "teach" others to use whatever Code we develop to delete errant entries. At the moment I've no idea what that Code will be but we'll develop something...perhaps...maybe...

Haven't thought this through or an approach to take as yet. Just thinking out loud...what Column can I have in Sheet Programs requested to use as a marker Column?

[SMcuthbert]

How does column "U" work? It's all yours!
I can't wait to see what we end up with here!

[jaslake]

Hi Sue

I've used Columns U and V.

In Cell I1 of Worksheet Programs requested is an Option Button. Click the Option Button and you'll be presented with a User Input Box. The User Input Box will ask you to Click on a Cell in the Row you wish to Remove. The Cell Address will appear in the User Input Box. Click OK. That Row will be deleted from Worksheet Program requested and the Chech Box on the Source School Worksheet will be unchecked and made active.

Let me know of issues.

Please Login or Register  to view this content.

[SMcuthbert]

THank you again for your hard work on this. I am headed on vacation and look forward to putting this to use upon my return. Take care and thank you again!

Sue

[jaslake]

Hi Sue

You're welcome...glad I could help. Thanks for the Rep.

Have a great vacation.

[SMcuthbert]

You bet, John!

[SMcuthbert]

Works like a charm. Thank you again!

[jaslake]

Your welcome.