Copying and Deleting Range using buttons on worksheet

[Svyatoslav Kuznyetsov]

Greetings everyone! I will really appreciate Your help in solving such
a problem. My table looks like this:
A B C D E F G
1 - Block of data
2 Block of Data
3 - Block of Data
4 Block of Data
5 +
What I need to do. By clicking on "+" macro starts and it copies
range (b1:g2). It is paste in (b5:g6). In A5 appears "-" and in a7
appears "+". It will look like this:
A B C D E F G
1 - Block of data
2 Block of Data
3 - Block of Data
4 Block of Data
5 - Block of Data
6 Block of Data
7 +
It also needs to have a reverse action - by clicking on "-" block of
data is deleted and all other data is moved up. Thank You very very
much.

 

[Nick H]

Hi, I posted a reply 2 days ago but Google seems to have lost it -
here it is again...

Try putting this in a standard module (beware line-wrap)...
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit

Private Const BlkHght As Long = 2
Private Const BlkWdth As Long = 7

Sub CopyBlock()
Dim rngSource As Range
Dim rngDest As Range

Set rngSource = ActiveSheet.Cells(1, 1)
Set rngSource = rngSource.Resize(BlkHght, BlkWdth)
Set rngDest = ActiveCell.EntireColumn.Find("+", , , , ,
xlPrevious)

rngSource.Copy
rngDest.Insert shift:=xlDown

Application.CutCopyMode = False
End Sub

Sub DeleteBlock()
Dim rngDelete As Range

Set rngDelete = ActiveCell.Resize(BlkHght, BlkWdth)

rngDelete.Delete shift:=xlUp
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

....and this in the Worksheet code module...

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Make sure only one cell has been selected
If Target.Cells.Count = 1 Then
' We're only interested in the first column
If Target.Column = 1 Then
If Target.Value = "-" Then
DeleteBlock
Application.EnableEvents = False
ActiveCell.Offset(1, 0).Activate
Application.EnableEvents = True
ElseIf Target.Value = "+" Then
CopyBlock
Application.EnableEvents = False
ActiveCell.Offset(1, 0).Activate
Application.EnableEvents = True
End If
End If
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you change your design later you can adjust to a change in block
size by modifying the constants.

Seasons greetings, Nick.