User of Workbook to Select How Many Times to Run a Macro.

[ekr]

I have a macro that copies and pastes a column. User of the workbook may need to run this macro once, or five times, up to at least 20 times in order to copy/paste the column 20 times if needed. I have created a drop down list with the numbers 1 through 20 (1, 2, 3, etc). How can I create a macro that states to run the macro the number of times specified by the user when they choose from the drop down list. Also, can I atached the macro to the number in the drop down list. So if user selects 2 - then the macro is run twice, etc.

[AndyLitch]

You need two macro's basically. One to detect the number of the dropdown and call the second "copy" macro the requisite number of times.
The basic format is like this:

Please Login or Register  to view this content.

[ekr]

Thank you for your help. I cannot seem to get this to work. It did not work with the drop down list or even just trying to run straight from the list of macros. Not sure what I have missed doing -- btw, I am running Excel 2010.

[AndyLitch]

Have a look at the code in this example
MultiRunEx.xls

Please Login or Register  to view this content.

[abousetta]

A simplified version:

Please Login or Register  to view this content.

abousetta

[ekr]

I cannot seem to get it to work. So, one dropdown menu and if the user selects 2, then the code runs twice and they get 2 columns, if they select 3 they get 3 columns, etc. Here is the code I use to just do the copy/paste.
Sub CopyDataToANtoherLocation()
Dim nEndC1 As Integer
nEndC1 = Cells (2, Columns.COunt).End(x1ToLeft).Offset(0, 1).Column
Range("A2:A20").Copy Range(Cells(2, nEndC1), Cells(20, nEndC1))

[abousetta]

Assuming your dropdown menu is in A1, use this code:

Please Login or Register  to view this content.

Change the Range("A1") according to your own data.

abousetta