Duplicate a Box depending on how many rows another tab has.

[donnydorko]

I need a macro to duplicate a box i have in a sheet depending on how many rows the other tab has. In this example file ive included the macro i have now. With this macro i select A1 : R23 and press ctrl shift d, it asks me how many duplicates i want and i enter the number based from how many the test2 tab has. I would like a macro to read how many rows the test2 tab has and then automatically duplicate the box that many times . In other words automate it, instead of me having to enter the number ( this number changes depending on the file i have ).

Ive included a test file with intructions and my current macro, i have 2 tabs which need to be looked at.


Thanks for any help.

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

You may need to reattach the CTRL-D to it after you replace the code. It acted odd for me until I did that. (Alt-F8, select, OPTIONS)

[donnydorko]

works perfectly but i still have to highlight the box A1:R23, i would rather it automatically duplicate the box , in otherwords that range instead of me highlighting it.

[JBeaucaire]

You want that hardcoded now?

What about if it's been run already, do you want it to clear the rows below the box range and reapply the new "number of rows needed"?

[JBeaucaire]

Maybe:

Please Login or Register  to view this content.

[donnydorko]

works like a charm, thanks sir