Hi,

I want a way of automatically inserting a new column from whicheve
cell is selected. I know how to do this, but everytime I do it I hav
to work my way down the colum formatting the cells to how I want the
to look. I have taught myself how to create a macro that does thi
procedure for me, but it always runs it from the same cell, and not th
cell that is currently selected.

Essentially I'm setting up a Register/Mark Book in Excel and want t
add colums for additional data as and when I need them. So the column
need to be entered from different places each time.

Sorry, if I'm not explaining this very well, but if anyone can deciphe
what I'm trying to say and has a suggestion then I would be incredibl
grateful.

Thanks,

Mark

--
mevett

-----------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...fo&userid=2913
View this thread: http://www.excelforum.com/showthread.php?threadid=48879

try
activecell

--
Don Guillett
SalesAid Software

"mevetts" wrote in
message ...

Hi,

I want a way of automatically inserting a new column from whichever
cell is selected. I know how to do this, but everytime I do it I have
to work my way down the colum formatting the cells to how I want them
to look. I have taught myself how to create a macro that does this
procedure for me, but it always runs it from the same cell, and not the
cell that is currently selected.

Essentially I'm setting up a Register/Mark Book in Excel and want to
add colums for additional data as and when I need them. So the columns
need to be entered from different places each time.

Sorry, if I'm not explaining this very well, but if anyone can decipher
what I'm trying to say and has a suggestion then I would be incredibly
grateful.

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

Sorry, but I'm a bit of a numpty! What's is activecell?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

the active cell

--
Don Guillett
SalesAid Software

"mevetts" wrote in
message ...

Sorry, but I'm a bit of a numpty! What's is activecell?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

Sorry, I know it's a pain with a newbie!

Do you mean that 'activecell' is a form of command in macro language?!
If you do then I'm not sure how to integrate the command into the macro
so it works.

Could you possibly explain in layman's terms?

Thanks for your time,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

As always, post your coding efforts for comments.


--
Don Guillett
SalesAid Software

"mevetts" wrote in
message ...

Sorry, I know it's a pain with a newbie!

Do you mean that 'activecell' is a form of command in macro language?!
If you do then I'm not sure how to integrate the command into the macro
so it works.

Could you possibly explain in layman's terms?

Thanks for your time,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

Try this

Option Explicit

Sub Test()

ActiveCell.EntireColumn.Insert

End Sub



*** Sent via Developersdex http://www.developersdex.com ***

I'm sorry, my knowledge is still -very- limited.

I intend to get the code that I have created tomorrow and post it here,
so that those more knowledgeable will have an idea of what I'm trying to
do!

Many thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

Here is a screenshot of what I'm working on. I thought the visual may
help explain what I'm trying to do.

On the left I have the pupils names and then next to each name their
attendance and marks.

However, as you can see I only have a few columns available and if I
want to insert additional columns I have to go through the procedure of
selecting where the columns should be inserted, insertting them and then
formatting them to match the layout. This is what I want to automate
using a macro.

However, when the macro is run will it insert the columns from the
current active cell? Or will it run and insert the columns according to
whichever cell was selected when the macro was recorded? If this is the
case I will be unable to continue adding new columns to the right of
the data.

Any help would be great....bearing in mind that I'm a complete novice
with macros!

Thanks,

Mark.


+-------------------------------------------------------------------+
|Filename: screenshotnew.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4078 |
+-------------------------------------------------------------------+

--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

Here is the macro I have so far created -

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 29/11/2005 by M.Evetts
'

'
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Range("Z4:Z9").Select
With Selection
..VerticalAlignment = xlBottom
..WrapText = False
..Orientation = 90
..AddIndent = False
..ShrinkToFit = False
..ReadingOrder = xlContext
..MergeCells = True
End With
Range("Y4:Y9").Select
With Selection
..VerticalAlignment = xlBottom
..WrapText = False
..Orientation = 90
..AddIndent = False
..ShrinkToFit = False
..ReadingOrder = xlContext
..MergeCells = True
End With
ActiveWindow.SmallScroll Down:=9
Range("Z23:Z28").Select
With Selection
..VerticalAlignment = xlBottom
..WrapText = False
..AddIndent = False
..ShrinkToFit = False
..ReadingOrder = xlContext
..MergeCells = True
End With
Range("Y23:Y28").Select
With Selection
..VerticalAlignment = xlBottom
..WrapText = False
..AddIndent = False
..ShrinkToFit = False
..ReadingOrder = xlContext
..MergeCells = True
End With
Range("Z23:Z39").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=-27
Range("B1").Select
End Sub


It does insert the new columns whichever cell is active, however, it it
does not apply the formatting to the newly inserted columns. Instead, it
keeps formatting the cells that where formatted during the recording of
the macro.

Is there any way of getting it so that it formats the cells in the
newly inserted columns?

Many thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=488798

where it says 'Range("Z4:Z9").Select' in the macro it needs somethin
that tells it to select the cells to the right? So it formats the cell
in the inserted columns.

Am I along the right lines

--
mevett

-----------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...fo&userid=2913
View this thread: http://www.excelforum.com/showthread.php?threadid=48879