button to insert 4 cells and move current data to the left or down

[ohmedic88]

How do I build a button to insert cells. Currently I have a sheet that I keep adding data to the left and it is getting out of hand. So I want to add a button to insert blank cells and push all the older data to the left. This way the newest data can be easily accessed and visible.

In this example I need to added new information in c3, c4, c5, c6 and all the existing data moved to c7,c8,c9,c10 and so on and so forth. I need to be able to add cells to any of the rows (Eric, Ron, Steve etc)

Also how hard is it to do the same to add cells that move down?

Thanks in advance.

Name

Original start date

Original Stop Date

Complete

name

Start

Stop

Complete

name

Start

Stop

Complete

name

start

Eric

xx/xx/xx

xx/xx/xx

yes

Eric

xx/xx/xx

xx/xx/xx

no

Ron

xx/xx/xx

xx/xx/xx

yes

Inserted

Inserted

Inserted

Inserted

older data

older data

older data

older data

Steve

xx/xx/xx

xx/xx/xx

no

Cathy

xx/xx/xx

xx/xx/xx

yes

Jessie

xx/xx/xx

no

Darla

xx/xx/xx

no

<tbody>
</tbody>

 

[pbornemeier]

The 1st sub will shift the adjacent selected cells in a single column 4 columns to the right.
The 2nd sub will shift the adjacent selected cells in a single row 4 rows down.

Option Explicit


Sub Insert4CellsToRightOfSelection()
    
    Dim rng As Range
    Set rng = Selection
    
    If rng.Columns.Count > 1 Or rng.Areas.Count > 1 Then
        MsgBox "Select one or more adjacent cells in a single column"
        GoTo End_Sub
    End If
    
    Range(Selection, Selection.Offset(0, 3)).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End_Sub:
End Sub
Sub Insert4CellsBelowSelection()
    
    Dim rng As Range
    Set rng = Selection
    
    If rng.Rows.Count > 1 Or rng.Areas.Count > 1 Then
        MsgBox "Select one or more adjacent cells in a single row"
        GoTo End_Sub
    End If
    
    Range(Selection, Selection.Offset(3, 0)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End_Sub:
End Sub