Macro/Addin to Shift Cells Right


J

Jerry

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
 
Ad

Advertisements

S

Sandy Mann

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
J

Jerry

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.
 
D

Dave Peterson

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.
 
J

Jerry

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
 
S

Sandy Mann

Jerry said:
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.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Ad

Advertisements

D

Dave Peterson

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.
 
Ad

Advertisements

J

Jerry

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top