Macro which inserts a column and cut and pastes data

[s b via OfficeKB.com]

Hi

I have read quite a few posts and still quite new to the whole macro world.
Basically what i am trying to achieve is to be able to insert a column at a
specfic location in a spreadsheet and then cut and paste the data via a macro.
At the moment this is carried out manually were i have to carry out one
function a number of times which can be quite time consuming.

The format is the following:

A B C D E F
1 SubName 2002 2003 2004 2005
2 Sub1 87 89 76
3 Sub2 96 97 98
4 Sub3 69 70 78

5 Sub4 76 75 76

6 Average 82 89 85

What i am looking to do is develop a macro which will insert a column and
bring 2005 data in, this is carried out yearly. At the moment one workbook
consists of about 5 spreadsheets with about 30 of the above tables so it
would be useful if an automated process could be put in place. Also these
tables have a graph each as well so ideally it wouldbe useful if these could
be updated as well.

I would be grateful for any guidance, thank you for your time

Look forward in hearing from you

 

[STEVE BELL]

The trick is knowing where the data is and where you want it to go.
Also - do you have formulas in the data, or is it just values.

As a general rule:

Dim col1 as Long, col2 as Long
' where col1 = data column
' col2 = column to insert

col1 = 2 ' or col1 = Range("A1").Value
col2 = 25 ' or col 2 = Range("A2").Value

Columns(col2).Insert
Columns(col1).Copy _
Destination:=Cells(1,col2)
Columns(col1).clearcontents

You can build various loops to do this multiple times.
If you are working with a number of sheets.

Dim ws as Worksheet

For each ws in ActiveWorkbook.Worksheets

Columns(col2).Insert
Columns(col1).Copy _
Destination:=Cells(1,col2)
Columns(col1).clearcontents

Next

Or you can specify each worksheet by name or index number.
You may want to specify ranges with the worksheet designation
ws.Columns(col2).Insert

hope this gets you started...

 

[STEVE BELL]

You're very welcome!

Keep us posted on your progress....

--
steveB

Remove "AYN" from email to respond

Hi Steve

Thanks for that much appreciated, i will attempt that now and see how i go

sb

The trick is knowing where the data is and where you want it to go.
Also - do you have formulas in the data, or is it just values.

As a general rule:

Dim col1 as Long, col2 as Long
' where col1 = data column
' col2 = column to insert

col1 = 2 ' or col1 = Range("A1").Value
col2 = 25 ' or col 2 = Range("A2").Value

Columns(col2).Insert
Columns(col1).Copy _
Destination:=Cells(1,col2)
Columns(col1).clearcontents

You can build various loops to do this multiple times.
If you are working with a number of sheets.

Dim ws as Worksheet

For each ws in ActiveWorkbook.Worksheets

Columns(col2).Insert
Columns(col1).Copy _
Destination:=Cells(1,col2)
Columns(col1).clearcontents

Next

Or you can specify each worksheet by name or index number.
You may want to specify ranges with the worksheet designation
ws.Columns(col2).Insert

hope this gets you started...

Hi

[quoted text clipped - 31 lines]

Look forward in hearing from you