Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make each column of data a new worksheet?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make each column of data a new worksheet?
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 --------------- Jacob Skaria "AbiC" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make each column of data a new worksheet?
Thanks Jacob - sorry to be so dense, but what is supposed to happen when I
run the macro? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "AbiC" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make each column of data a new worksheet?
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. 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 --------------- Jacob Skaria "AbiC" wrote: Thanks Jacob - sorry to be so dense, but what is supposed to happen when I run the macro? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "AbiC" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make each column of data a new worksheet?
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" wrote: 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. 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 --------------- Jacob Skaria "AbiC" wrote: Thanks Jacob - sorry to be so dense, but what is supposed to happen when I run the macro? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "AbiC" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make each column of data a new worksheet?
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 --------------- Jacob Skaria "AbiC" wrote: 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" wrote: 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. 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 --------------- Jacob Skaria "AbiC" wrote: Thanks Jacob - sorry to be so dense, but what is supposed to happen when I run the macro? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "AbiC" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a column not allow new data for monthly report? | Excel Discussion (Misc queries) | |||
How do I make a column of data sort into rows? | Excel Worksheet Functions | |||
How do i make cells in one worksheet copy certain data to another | Excel Worksheet Functions | |||
Using a column of data from 1 worksheet to extract data from another worksheet | Excel Worksheet Functions | |||
how do I make a copy of a worksheet and retain formulas but not data | Setting up and Configuration of Excel |