Macro/Addin to Shift Cells Right

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry - 18 Sep 2007 23:20 GMT

I convert a lot of text files to Excel format and often have to shift
various cells to the right. I know how to use the insert action to shift
the cells to the right but am wondering if there is some way I can create a
Macro to do this. If there is an addin I could use, I'm willing to try it.

Thanks for any help.

Jerry

Reply to this Message

Sandy Mann - 18 Sep 2007 23:41 GMT

Sub MoveIt()
ActiveCell.Insert Shift:=xlToRight
End Sub

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I convert a lot of text files to Excel format and often have to shift
> various cells to the right. I know how to use the insert action to shift
[quoted text clipped - 6 lines]
>
> Jerry

Reply to this Message

Jerry - 19 Sep 2007 02:17 GMT

In this Macro, the cell shifts to the right one column. If I want to shift
the cell four columns, do I change the "x1" to "x4"

The Macro works fine and I've set up a "hot key" to exercise the Macro. I
tried adding an ICON to the toolbar, but couldn't find the Macro on the
commands list.

"Sandy Mann" <sandymann2@mailinator.com> wrote in news:#zhc$Tk#HHA.700
@TK2MSFTNGP05.phx.gbl:

> Sub MoveIt()
> ActiveCell.Insert Shift:=xlToRight
> End Sub

Reply to this Message

Dave Peterson - 19 Sep 2007 03:15 GMT

That's actually XLToRight (ex-ell, as in xl=excel)

You could do something like this:

Sub Moveit()
ActiveCell.Resize(1, 4).Insert shift:=xlToRight
End Sub

Make sure this is in a General module (not behind a worksheet).

And you may want to try the hotkey again once you put that code in a general
module.

Tools|macro|Macros
select Moveit
and click the Options button
assign your shortcut combo there.

> In this Macro, the cell shifts to the right one column. If I want to shift
> the cell four columns, do I change the "x1" to "x4"
[quoted text clipped - 9 lines]
> > ActiveCell.Insert Shift:=xlToRight
> > End Sub

Signature

Dave Peterson

Reply to this Message

Jerry - 19 Sep 2007 06:22 GMT

Mr.Peterson,

As you can see by my comment regarding "x1", I'm not well versed in VBA.
So perhaps you can understand my confusion when you mention "General
Module". When I cut and paste the script to the Macro, the word "general"
appears at the top of the screen to the right. Is this a "general module"?
If it is, I still can't seem to get the Macro to show up in the Macro
option for customizing my toolbar. I've been searching the Web to find out
about "general modules", but have had no success. Is there somewhere I can
get this information or maybe you can give me a quick explanation.

Thanks
Jerry

> That's actually XLToRight (ex-ell, as in xl=excel)
>
[quoted text clipped - 13 lines]
> and click the Options button
> assign your shortcut combo there.

Reply to this Message

Sandy Mann - 19 Sep 2007 09:47 GMT

> Module". When I cut and paste the script to the Macro, the word "general"
> appears at the top of the screen to the right. Is this a "general
> module"?

If you mean in the LEFT-hand box at the top of the module screen, then yes
that is a General module.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Mr.Peterson,
>
[quoted text clipped - 30 lines]
>> and click the Options button
>> assign your shortcut combo there.

Reply to this Message

Dave Peterson - 19 Sep 2007 12:14 GMT

If you saw that (General) under the worksheet module (rightclicking on the
worksheet tab and selecting view code), then this is not the correct location.

Open your workbook
open the VBE (hit alt-f11)
Hit ctrl-r to see the project explorer (like windows explorer)
Click on your project to select it
Insert|Module (from the menu bar)

You'll see Module# inside the project explorer. This is where the code goes.

And you should remove the code you have in the other locations.

> Mr.Peterson,
>
[quoted text clipped - 27 lines]
> > and click the Options button
> > assign your shortcut combo there.

Signature

Dave Peterson

Reply to this Message

Jerry - 19 Sep 2007 22:40 GMT

I want to thank Sandy and Dave for all of their help with my question. I
really appreciate the time and effort they gave to help me. I finally
managed to create the Macro and add it to my toolbar.

Again, thanks a bunch.

> I convert a lot of text files to Excel format and often have to shift
> various cells to the right. I know how to use the insert action to
[quoted text clipped - 5 lines]
>
> Jerry

Reply to this Message