VBA - Macro issue copy/paste line other sheet + duplicate current sheet

Hello,

First of all, this forum rocks ! Thanks a lot to the community.

I have an issue with a macro I am trying to create. Enclosed is the file I am working on.

- The sheet "WIN" is where I enter my data
- Those data are compiled in the table from line A59 until N59

--> When I click on the button "Add" (in the same sheet), I would like my macro to do 2 things:

1) Copy & paste all the data from A59 until N60 in the sheet "OFFER", just under the line where "-" is. The tricky thing is that I have to create various lines in the sheet "OFFER" with different data, coming always from the sheet "WIN" (cells from A59 to N60). Therefore I want my macro to understand that every time I click the "Add" button, it has to copy / paste the lines in the "OFFER" sheet under the last line containing "-".

2) Duplicate the sheet "WIN" in the same workbook with the name "WIN1", "WIN2", WIN3", etc. each time I click again on "Add"

Thanks a lot for the help !

Offer1.xlsm

Hello stnkynts !

Thanks a lot for your fast answer and the job you've done ! The code looks really useful for my need !

Unfortunately I cannot change the "button 27" to ActiveX as I am using a mac... (already know what you're thinking... )
Excel for Mac doesn't support ActiveX...

Do you see any way we can by-pass this issue ? (apart from buying another computer !)

Thanks a lot !

Please Login or Register  to view this content.

Thanks stnkynts !

Your macro works fine, just magic !
However is it possible to modify two things (I tried to do it by myself, but impossible... )

I would like to always stay on the sheet "WIN" when I click the button "Add". I explain myself; the original sheet "WIN" is only a basis for me to enter the data, and all the extra sheets "WIN1", "WIN2", "WIN3" are backups sheets for the data I change every time in the original "WIN" sheet. I won't be using them anymore.
So, when I got back to the initial "WIN" sheet, change the data and click on the "add" button again, I get the following error: "That sheet name is already in use.Enter a sheet name that is not already in use by another sheet".
Debug:

Please Login or Register  to view this content.

Each time I click on the "add" button in the sheet "WIN", I have also another button created in this same sheet. I don't know why, but I don't really need it (due to the reasons mentioned above).

I think with those changes, the macro will just fit perfectly to my needs !
Thanks thanks thanks a lot

It's no problem to do what you asked. I just didn't understand that was the intent in the beginning. No big deal. To make this easy I would like to use a random cell in the "WIN" worksheet so I can assign a number to it. I will then use that number as a counter for the "WIN1", "WIN2", etc. Based upon your "WIN" sheet I am going to arbitrarily assign cell K1 to serve that function. I will format that cell so you see no text.

Please Login or Register  to view this content.

*I'll take some rep if this concludes your question.

Hello stnkynts !

Sorry for my late reply.
A very big, huge THANK YOU for your macro. It works just fine, and as I wished... You saved me a lot of time and headaches...

Just one thing: in my previous post I mentioned that "Each time I click on the "add" button in the sheet "WIN", I have also another button created in this same sheet. I don't know why, but I don't really need it (due to the reasons mentioned above)."

Well, this button still shows up, and I don't know why. Each time it is called "button 26", then when I click again on the button "add", it create another one with the name "button 29", etc... Always by three. And even stranger, it also creates me new drop lists just at the left side where I have my "SLAT" and "NAME" drop lists...
I tried to see what in your macro could cause that, but I couldn't find out. Enclosed you will find the excel file I am working on, so you will be able to see it.
Do you know what is causing it ?

But again, I only have this issue. Everything else works just fine, so THANKS A LOT !

Have a nice day,

Offer1macro.xlsm

UPDATE: I think I found where the issue comes from:

I removed those two lines:

Please Login or Register  to view this content.

Removing this, everything works as expected.

Could you please confirm this was the solution ?

Thanks a lot !

Yes, you are correct. I kept those lines there because your original post asked to have them copied to the next sheet. Removing those 3 lines is perfectly fine.

Perfect, thanks a lot stnkynts, you rock !!!!
BIG THANK YOU for your help !, my macro works just perfectly fine.

Hello stnkynts,

I have one last problem that I cannot solve; when my macro creates the new sheet called "WIN1", what piece of code should I insert if I want all the cells from "WIN" to be copied and pasted in "WIN1" as VALUES ONLY ? (I mean if I have links to another sheet, or formulas, how to make them disappear ?)

Here is my current code:

Please Login or Register  to view this content.

I tried to play with the following piece of code, but couldn't find it out:

Please Login or Register  to view this content.

Thank you !

Please Login or Register  to view this content.

Thanks for your quick reply stnkynts.

Your macro works, but unfortunately this is not what I was looking for... For me the fact to copy the sheet was better (keeping me formats and buttons in case I have to modify each one later).
Let's says I still want to copy my sheet "WIN" to "WIN1", but ONLY cells from B2 to C13 has to be pasted as values (to avoid links to another external sheet). Keeping this initial piece of code, is it possible ?

Please Login or Register  to view this content.

I promess, after that, I won't be bothering you anymore !
Thanks a lot for your help !

Just come in afterwards and recopy that range and paste as values.

Please Login or Register  to view this content.

Hello stnkynts,

Perfect ! THANKS, THANKS, THANKS. Just what I needed.
I had to make some adjustments in the sheet "WIN", because I had some merged cells and this caused an error at the time of the ".PasteSpecial xlPasteValues", but once those merged cells removed, it works just fine.

A very big thank you stnkynts !