Excel VBA - Copying button down multiple rows whilst keeping relative positioning

[Marco123]

Hi all,

I have several buttons in a row on a spreadsheet that I am trying to copy down multiple rows in VBA.

I know how to copy a button in VBA with the following code:

ws.Shapes("Button 1").Copy
ws.Range("J10").Select
ws.Paste

From this code, it is easy to loop it to copy down the button multiple rows. However, it copies the button to the left hand side of the cell each time and loses the original positioning (in the centre of the cell) of the first button. Therefore, I am trying to find out how to copy the buttons whilst keeping the original positioning relative to the cell of the button I am trying to copy.

Any ideas? It needs to be scalable to say copy down 50 rows.

Thanks all.

 

[combo]

It's possible to read shape's Width and Height and range's Left, Top, Width and Height. Basing on this calculate and set Left and Top for copied shape.

combo

 

[SkipVought]

Just curious about all the buttons on the sheet.

Could you explain?