ExcelBanter - Macro for new column

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro for new column (https://www.excelbanter.com/excel-programming/346749-macro-new-column.html)

Macro for new column

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

Macro for new column

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

Macro for new column

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

Macro for new column

Try this

Option Explicit

Sub Test()

ActiveCell.EntireColumn.Insert

End Sub

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

Macro for new column

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

Macro for new column

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

Macro for new column

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

Macro for new column

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