Simple VBA Help

[retractedspace]

I know this will sound very simple for members but I have never used VBA.

I want a macro in my excel to when I use the shortcut CTRL + W to run a macro which does the following

Takes the cell I am in copies it
Then inserts it 5 columns to the right pushing the other columns right as it inserts
Then to delete data in the in the 5 cells to the left.. with the slected cell back where I started ready for new data

The VB needs to ignore what Row its on ie it is not always run on row 2

Going From (Blue indicates selected cell)

A	B	C	D	E	F	G
Date	Text 1	Text 2	Text 3	Text 4	Date 2	{Blank}

To (Blue indicates selected cell)

A	B	C	D	E	F	G
{Blank}	{Blank}	{Blank}	{Blank}	{Blank}	Date 1	Date 2

 

[jim may]

You say:
Then inserts it 5 columns to the right pushing the other columns right as it inserts

If you are currently on Row 4 (for example) do you wish to truly Insert 5 Worksheet Columns, or do you wish to "move" only row 4 data 5 columns to the right? Advise.

 

[retractedspace]

I have data in Columns
A, B, C, D, E, F and G

I want the data in Column A (only the cell selected not the entire column ) to be copied and the inserted into Column F the data that was in F pushed right into G (Shift:=xlToRight) as there could be a long set of data in H, I, J, K etc.

The data then in A, B, C, D and E deleted leaving the selection back on A ready for new data to be input.

 

[NilsMoon]

Hi.
A good way to start is to record macros and look what the recorded script looks like.

Do you mean pushing all other columns to the right? then its like this:
Sub fiveright()
'
' fiveright Macro
'
' Keyboard Shortcut: Ctrl+w
'
Range(ActiveCell.Offset(0, 1), Selection.End(xlToRight)).Select
Selection.Cut
ActiveCell.Offset(0, 5).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -6).Select
Selection.Cut
ActiveCell.Offset(0, 5).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -5).Select
End Sub



If you just want to push the F column to the right its like this:
Sub fiveright()
'
' fiveright Macro
'
' Keyboard Shortcut: Ctrl+w
'
Range(ActiveCell.Offset(0, 5),Selection.End(xlToRight)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -6).Select
Selection.Cut
ActiveCell.Offset(0, 5).Select
ActiveSheet.Paste
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -4)).Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Select
End Sub

Nils

 

[retractedspace]

ok I have recorded the macro, but I need it to use the row I am on not always go back to Row 4.

Sub Macro3()
'
' Macro3 Macro
'

'
Selection.Copy
Range("K4").Select
Selection.Insert Shift:=xlToRight
Range("F4:J4").Select
Range("J4").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("F4").Select
End Sub