Copy and pasting into the next available space

[Cjax]

Hi everyone:

I'm brand new to VBA so i don't know how to proceed. I'm familiar with recording a macro but I don't think that will be enough to solve my problem. I've attached a spreadsheet with comments on what I'm trying to accomplish. This seems very complicated to me so I'm very appreciative of your help.

Thank you in advance to all of the brilliant people on this forum who have already helped me many times!

[stnkynts]

Hi Cjax.

While helping you to build the macros for this sheet it might be easiest to take it in a couple of steps rather than give you all the macros to do what you want for everything (in case we need to make some adjustments).

I have the macro for the buttons on the right side set up for you. You mentioned that you want one button per line. That is 20 macros. It would be easier to just create one macro and archive all the "action steps" but for your reasons you want individual buttons. I am including the code for 2 of the buttons. You will need to create the other 18 by just copying and pasting and editing the ranges. Let me know if you don't know which ranged to edit.

FYI - One of the features that I added was kind of an error checking for if the Archived Action Steps is full for each number. If so you will be prompted to clear out the list if you so choose.

Please Login or Register  to view this content.

Once we get the right side working for you I will help you with the left.

[stnkynts]

Forgot to add the part of moving from the next step columns. Should look like this for each button (editing ranges accordingly):

Please Login or Register  to view this content.

[Cjax]

Dude!!! This looks awesome...please forgive me for my ignorance but how do I add this and should I be using just your most recent post or a combination of both posts?

i believe I have to go to the VBA editor in Excel, click on the worksheet with the data, change general to worksheet and copy and paste the code? Is that right?

Thanks for this

[stnkynts]

No problem....we all have to learn at some point. To access the visual basic editor through excel one of the ways to do it (excel 2007) would be to add the developer tab (in Excel Options -> Popular -> 3rd box). Under the developer tab you would click visual basic. Right click on this workbook then insert -> module. That is were you would copy and paste the updated code.

By the sounds of it you have very little experience with vba, which is perfectly fine. Try to recreate the macros for each button, using copy and paste, and adjust the ranges to fit. If you are having a hard time doing it i can help you with a couple more.

The last step would be to right click on the button itself and select assign macro. Choose the appropriate macro as you will have 20 macros, when complete, one for each line as you desired.

[Cjax]

Hey - I actually was able to assign the macro to the button!! Whoo Hooo Thank you - It does one of the things I was having a hard time doing by moving the action step to the archived steps box but it's not moving next step 1 to action step or next step 2 to next step 1. What should I do next?

Thanks again

[Cjax]

Hi stnkynuts:

i figured out what I did wrong and was able to fix everything on the right side. Any ideas on the left side. The only thing I thought to add to the left side that wasn't in the text box of the attached document was a warning asking if you really wanted to archive the entire file.

Thanks again for your help so far and for anything else you can do.

Cjax

[Cjax]

Hi everyone - if anyone else can help me with the piece that stnkynts hasn't I would be very appreciative.

Thanks!!

[jaslake]

Hi Cjax
How 'bout posting a workbook with the code you have working. I'll look at it.

[Cjax]

Hi jaslake:

Sorry i didn't see this until now. The code I have seems to be doing what I need it to but there is a bunch of code that still needs to be added for additional functionality. I have included a text field in the workbook that explains what else I'm trying to accomplish.

Also - I have the text "Last Update" in I1...I had a macro that would put the last updated date and time in I2 but it stopped working. Do you have anything that would update that field with date and time anytime a change is made in the sheet?

Thanks a lot for taking a look!

Cjax

[jaslake]

Hi Cjax
Put some data in your worksheets so I can get a picture of what you wish to accomplish (before and after).

[Cjax]

Jaslake:

I've added some data. If you look on the archive sheet it shows a client that the sales person is not longer actively persuing. That's why I'd like the ability to move that client and all affiliated information to the archive sheet and then leave a blank line and detail box in it's place.

[jaslake]

Hi Cjax
I've looked at your sample file. You've got merged cells going on and VBA doesn't like them. What does Archive worksheet look like when you archive the next item? Does it create a new "Archive Block" of data below the existing "Block(S)" of data or does it overwrite the existing "Block" of data?

Where does data to fill Top 20 List worksheet Rows 31-40 and the attendant columns come from (I know where "Archived Steps" comes from)? Or is this the "additional functionality" you referred to?

Trying to understand the process.

[Cjax]

Hey John:

In terms of archiving, it would ideally create a new "Block" of data below the existing "Block(s)" of data. That and adding a macro that shows the last time the sheet was changed is the only additional functionality I'm looking for.

The rest of the data for rows 31-40 will be entered directly by the sales person.

[jaslake]

Hi Cjax

This'll take a little time. Schools are closed (until further notice) due to flooding so, I have Grandson duty tomorrow (until further notice...love that duty...even more than the Forum).

I, or someone, will get back to you ASAP.

[Cjax]

Hi John:

Thanks for your response and have a great time with your grandson!! If anyone else can help in the meantime I would very much appreciate it.

Thanks,

Chris

[Cjax]

I could have sworn i posted this earlier...But...Thank you John for your response and I hope you have a great time with your grandson.

If anyone else can help in the meantime I would be very appreciative.

Thanks again to all

[stnkynts]

Ok here is the left side. I was busy all weekend so i didn't get a chance to work on it for you. In accordance with your wishes I set up a userform for you which activates when you click the "Button4" on the left side. It then prompts you to select which of the 20 clients that you want to archive.

Notes:
1. You must leave the template in the archive sheet that is already there. You can clear the current contents out if you want but I use that to generate new files for each client.

2. I unmerged your few cells. VBA and merge do not get along.

3. YOU WILL NEED TO FINISH CODING THE OTHER 17 CHECK BOXES. It is actually really easy, just time consuming. I did 3 for you already. Goto Visual Basic Editor -> UserForm1 -> Double click on the Okbutton in the userform (which should open up the Sub command for the Okbutton_Click). In there you will see the code for the first 3 check boxes. Each is named Client1, Client2, Client3, etc to 20. Just copy and paste, change from client3 to client4, respectively, and change the ranges where i have indicated.

Let me know if you have anymore questions.

[jaslake]

Hi Chris
It appears stnkynts is back in the saddle (as I am this evening). I've been playing with this a little bit and it really depends on what approach you wish to take.

If you wish to conform the left side to the approach on the right side, this code will accomplish such

Please Login or Register  to view this content.

You need only one procedure to handle all the buttons. The same approach could be taken on the right side...one procedure for all buttons.

stnkynts has spent considerable time with you on this and has developed what appears to be a solution for you. Perhaps he can use the code in the attached to enhance his contribution.

[Cjax]

Guys!!! You are both awesome...I'm trying to combine the code from both of you. I can't tell you how much i appreciate your help. I'd take you both out for drinks if we were near each other.

Once I finish I'll post the final project.

BTW - does anyone have a macro for automatically showing the date and time the sheet was last updated?

[jaslake]

Hi Chris

Ohio's next door neighbors

I'd take you both out for drinks if we were near each other.

Need help, holler.

[jaslake]

Hi Chris

Regarding this

BTW - does anyone have a macro for automatically showing the date and time the sheet was last updated?

You indicated you had code that did as you required and stopped working

I had a macro that would put the last updated date and time in I2 but it stopped working

Post that code...I'll see how it fits (or doesn't)

[Cjax]

Hi John:

I found the code online but have deleted it once it stopped working and can't seem to find it again. I'll keep looking for another one.

Chris

[jaslake]

Hi Chris

What is it you wish to happen...anytime ANY cell changes on Top 20 List, the data and time gets updated?

[Cjax]

Hi John:

Yep - Anytime ANY cell changes in the Top 20 worksheet. i just tried something else and I keep getting an error.

[Cjax]

Here's a copy of what I was trying

[jaslake]

Hi Chris
Let me play with it for a bit.

[jaslake]

Hi Chris
Try this code in your Worksheet Change Event macro

Please Login or Register  to view this content.

and add this line of code to Module1

Please Login or Register  to view this content.

This prevents the code from running an eternal loop, which fills up the "memory stack" (I think).

Or simply change the Target Range

Please Login or Register  to view this content.

[Cjax]

Hey John - I changed the target range and that worked fine! but....why?

Thanks,

Chris

[jaslake]

Hi Chris
I'll explain it but I'd like you to SEE it for yourself.

With this code

Please Login or Register  to view this content.

I2 is PART OF THE TARGET RANGE, so EVERY TIME I2 changes, the change macro fires creating an infinite loop.

Put your old code back and place a break point on the above line...step through the code (F8)...watch what happens.

This stuff is fun...it can drive you nuts...stepping through the code sometimes (not always) restores sanity. Let me know if you have further questions.

By the way, if you need help modifying Module2 such that it doesn't need a separate procedure for each command button, let me know.

[Cjax]

I get it! Makes perfect sense. After this experience I'm very motivated to learn how to do this on my own.

Are your schools opened up again?

[jaslake]

Hi Chris
Glad to help. Yes, this stuff can be a tremendous time saver. It TAKES time but the payback is fantastic. Best of luck in your venture. If I can be of help, let me know.

Schools will reopen Monday...flooding receded last night to the point where main roads are now passable. Thanks for asking.