Drop down menus stop working after using a macro to copy them...

Question

Hello Guys!

I hope you are good, This is my first post, so please be kind with me,and I am not a programmer

Objective : I want to make an Excel file where by pushing a button a macro creates a copy of a form with dropdown menus and everything. Furthermore I want Excel to create a line in a table of content with only the relevant information of the created sheet that still has to be filled in.

What i have done, I have 3 sheets to start with: 1) Overvieuw table 2)background (basis for the dropdown menus) 3) the form which is operational and works. All the formulas are "fixed".

As I told you I am not a programmer, so I use the recording option to create my macro. this are the steps:

Start in A1>Select Sheet"form">right click and copy>in the Box,I select the place to put the file and select copy and ok. > this works the first time(when recording).

When I run the macro a second time, all the drop down menu's from my blanc sheet do not work any more and the on one the generated one do not either. Still the first generated copy works. The manual copies I made before are still working as well. 

Please help me! :D I hope I was clear enough and I thank you already...

Answer 1

Welcome to this forum.

Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

I have to look in your file to recognize what you want to achieve.

Andreas.

Answer 2

Dear Andreas, 

Thank you for your answer. Below you will find the link to the excel file as requested.

https://drive.google.com/file/d/0B5UHgWGmZCP6cEJkd01NLXN1Q1E/view?usp=sharing

Thank you for your help!

Answer 3

I guess you've made a mistake, the drop downs in "Sortie MM-DD" did not work, but seems ok in "Sortie 11-25".

I've corrected your file and written a macro to copy sheet "Sortie MM-DD".

https://dl.dropboxusercontent.com/u/35239054/Samples/e246ec62-413a-4c86-83aa-5c1a80a55f70.xlsm

Downlaod this file and execute "CopyForm".

Andreas.

Answer 4

Thank you for your answer and the writing of the code!!!

 It works fine, but I have a question, because the name of the Worksheet will change at every copy, how do I reference her(the worksheet) in my first tabel, to copy the cells from that worksheet into the tabel?

Thank you for your answer.. you are awsome!!!

Answer 5

how do I reference her(the worksheet) in my first tabel, to copy the cells from that worksheet into the tabel?

To copy the cells, resp. the contents to the first worksheet with VBA is not so easy, especially when the layout changes.

I suggest that you use a INDIRECT formula an get the values from the created worksheet. Use a cell in each row for the worksheet name, so you have to write the name only once and can drag the formulas down.

Andreas.