Creating a Macro / GUI that is continuous and does not loop

Hi, not sure what title to give my problem.

What I want to do is to create a button in Excel, where the user clicks it and another table pops up on the right of the original table. The user will need a variable amount of duplicated tables, hence, i would need the button to not loop and delete the previous table.

For instance, the sheet will ask the user to put in some data in a table (and the table has many formulas). I have many users, and each user may need different amount of tables ranging from 2 tables to 100. I would like a button for them to click as many times as they one so that the similar table (with all the formulas attached) will be copied to the right of the orginal table.

Basically, if they click the button 5 times, 5 tables will appear.

Any suggestions how to do this?

Vishesh's picture

Insert any button in your

Insert any button in your sheet and link the following macro to it. Select any cell in the table before running the macro.

Sub CopyTable()
Dim rngTbl As Range
Dim rngTgt As Range

Set rngTbl = Selection.CurrentRegion
Set rngTgt = rngTbl.Cells(1, 1).Offset(, rngTbl.Columns.Count + 1)

rngTbl.Copy
rngTgt.PasteSpecial xlPasteAll

Application.CutCopyMode = False

rngTgt.Select

Set rngTbl = Nothing
Set rngTgt = Nothing
End Sub

Creating a Macro / GUI that is continuous and does not loop

Hi Vishesh. Thanks for your assistance.

While running the macro, a message poped up.

"Run-time error '438'.

Object does not support this property or method."

When i debug it, this code is highlighted.

Set rngTbl = Selection.CurrentRegion

Any solutions?

Vishesh's picture

Select any cell in the table

Select any cell in the table first; then run the macro.