Code to allow user to choose a sheet a copied cell is pasted to

[tancho321]

I have got a basic sheet and code that allows me to copy a cell and paste it to the first available cell in a given column on another sheet. I have 3 sheets that are all laid out the same but want a box to pop-up when the Macro button is pressed that allows the user to specify which sheet they are pasting the cell into.

On the example attached, if I select any of the buttons the code will allow the data to be copied and pasted across to the first blank cell in the set column on Sheet"Autumn" but what I am looking to do is for a pop-up to be shown that allows the user to select whether the data drops in the first blank cell and given column in either Autumn, Spring or Summer.

The code I have used so far is:

Please Login or Register  to view this content.

which works fine for pasting to the first available cell in the given column in Sheet"Autumn" but I can't find how to offer the options. Any help would be gratefully received.

Many thanks.

Test Code 1.xlsm

[tancho321]

Sorry about that! Hopefully, I have done it correctly now.
Thanks

[stnkynts]

Well if you want to add code to the front of each of your nine macro buttons you could do it this way:

Please Login or Register  to view this content.

However, I would suggest using a UserForm to do what you want. I have attached your workbook with a new single command button to launch a userform. It it not clear how this will work outside your example but I am sure you can edit it to work with your real workbook. Test Code 1.xlsm

Here is the code for the userform:

Please Login or Register  to view this content.

[tancho321]

Hi,
Thank you for your help. Please excuse me as I am very new to VBA and am trying to teach myself. I tried the first piece of code but it will only paste the response into Autumn despite trying Spring or Summer. I can see the benefits of the UserForm too but can't seem to be able to adapt it. I have attached my layout for the final workbook. On the first tab there a list of buttons (not working yet) under each heading with the same text in a table on the fourth tab (Y1Lookup). The idea is that a user selects one of the buttons and is asked which term they want to copy to (Autumn, Spring or Summer) and then it copies the corresponding text from the Y1Lookup tab and pastes it into the first available cell in the corresponding column in Autumn, Spring or Summer. The first code I used will copy it into the first cell in Autumn but I can't find a way to offer the choice.
Is this possible using any of the solutions you mention above? Ideally a drop down as opposed to typing if possible?
Many thanks again for your time and efforts.

iPlan v1.0 Layout.xlsx

[stnkynts]

I don't normally go this far into people's project because of the time investment but here you go: iPlan v1.0 Layout.xlsm
Just click the giant command button that says "click me" on the Year 1 Core sheet

Keep in mind a couple of things.
-The second combobox populates based upon the first combo box and it will incorporate any new text items you add into the lookup sheet as Long as you DON'T CHANGE THE SUBJECTS LOCATION FROM ROW 3 (ie reading, grammer, etc). You can add new subject to the next column in row 3 or new text at the bottom of each column all you want, just don't try to insert a row at row 3 which would shift everything down.

- This was tricky because of all the Merge&Center you did. Merging and Centering destroys the ability to use macros. Don't ask why.....it just makes life a pain when coding.

- I assume you will be adding more subject to the autumn, fall, spring sheet. This is perfectly fine. Just make sure you keep the same format with the subjects in row 3.

- Feel free to add more sheets for more years and so on. As long as the name for the "Y1 Lookup" doesnt change you should be good.

- You can edit the look of the userform to your hearts content. Just make sure you don't change any of the names of the comboboxes or commandbuttons

Failure to comply with these may result in faulty code, and im not going to go back and fix it. Have a good day.

[tancho321]

Wow! Thank you so much for your time and help with this. I really appreciate it.