ActiveX Command Button Problem

[cgfrank]

This is my first attempt at using vba or activex controls so please bear with me.

I created a command button in my document to use a macro (that I recorded) to copy these 2 boxes (merged) and duplicate them. It works well, but the new boxes appear above the old ones (where I drew the red line in this picture). I want them to appear below (as seen in the picture).

When I recorded the macro, I selected the "Action Item" cell and the one beside it to the right, copied it, inserted it below, and it worked. For some reason when I run the macro it inserts it above.

Can anyone help me understand why this is happening? I want it to appear below.

Thanks in advance.

[/URL]screenshot windows[/IMG]

 

[Yongle]

It would help if you post the VBA
Click on the code tags icon # (above post window) and paste your code between the tags

thanks

 

[cgfrank]

Sub ActionTDG()'
' ActionTDG Macro
'


'
    Range("ActionTDG1:ActionTDG4").Select
    Selection.Copy
    Range("ActionTDG1").Select
    Selection.Insert Shift:=xlDown
End Sub

 

[Yongle]

Try putting the cursor in the cell where paste is required and then running this

Sub ActionTDG()
    Range("ActionTDG1:ActionTDG4").Copy
    ActiveCell.Insert Shift:=xlDown
End Sub

 

[cgfrank]

Nope, no luck.

 

[Yongle]

1. Can you please explain exactly what this range is

Range([COLOR=#ff0000]"ActionTDG1:ActionTDG4[/COLOR]").Select

2. Please explain exactly what these 2 boxes (merged) are
- how were they created, are they textboxes, if so which type etc

thanks

 

[cgfrank]

Those two cells are cells spanning that row (ActionTDG1 is the defined name of the leftmost cell and ActionTDG4 is the defined name of the rightmost cell).
They were created by merging several cells into one larger text box, and then right click > define name.

 

[Yongle]

It is not possible to merge several cells into one larger textbox
- several cells can be merged into a single merged cell
- cells cannot be merged into textboxes - in Excel they are totally different objects
- they are either cells or textboxes but cannot be both

I think you are talking about merged cells

In which case, this is the code that you need

Sub ActionTDG1()
    Range(Range("ActionTDG1").MergeArea, Range("ActionTDG4").MergeArea).Copy
    ActiveCell.Insert Shift:=xlDown
End Sub

Select the cell that you want to paste into and run the code

Good luck

 

[cgfrank]

Thanks for all your help! I achieved what I wanted with a combination of your advice and ActiveCell.Offset(1).Select

 

[Yongle]

Thanks for the feedback