How to make each column of data a new worksheet?

[AbiC]

Hello,

I was hoping somebody could help me with an Excel problem - I am not a very
advanced excel user but I am hoping someone can give me some simplified
advice.

Basically I have got an Excel 2007 worksheet with 250 odd columns and I need
to split each column of existing data onto a new worksheet. Column A is
actually my list of headings, so ideally I would like to extract Columns A
and B into one worksheet, then Columns A and C, then Columns A and D etc, all
into new worksheets in the same workbook without having to manually copy and
paste each of them.

I am starting to suspect that this may require a macro or similar (which is
way over my head) but if you have any ideas I would love to hear them.

I hope that makes sense!

Thank you.

 

[Jacob Skaria]

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub SplitSheet()
Dim ws1 As Worksheet, ws2 As Worksheet, lngCol As Long, lngLastCol As Long
Set ws1 = ActiveSheet
lngLastCol = ws1.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For lngCol = 2 To lngLastCol Step -1
Set ws2 = Worksheets.Add(After:=ws1)
ws1.Columns(1).Copy ws2.Columns(1)
ws1.Columns(lngCol).Copy ws2.Columns(2)
Next
End Sub


If this post helps click Yes

 

[AbiC]

Thanks Jacob - sorry to be so dense, but what is supposed to happen when I
run the macro?

 

[Jacob Skaria]

would like to extract Columns A and B into one worksheet, then Columns A
and
As mentioned above; the macro would create new sheets by copying information
from ColA/B, ColA/C, ColA/D etc; from the active sheet

If this post helps click Yes

 

[AbiC]

I'm sorry Jacob - I can't make it work Nothing is happening when I run the
Macro - no new worksheets are appearing.

 

[Jacob Skaria]

Oops.my mistake...try this version

Sub SplitSheet()
Dim ws1 As Worksheet, ws2 As Worksheet, lngCol As Long, lngLastCol As Long
Set ws1 = ActiveSheet
lngLastCol = ws1.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For lngCol = lngLastCol To 2 Step -1
Set ws2 = Worksheets.Add(After:=ws1)
ws1.Columns(1).Copy ws2.Columns(1)
ws1.Columns(lngCol).Copy ws2.Columns(2)
Next
End Sub

If this post helps click Yes