PDA

View Full Version : Copy columns to new sheets



Dreamer
06-22-2005, 06:17 AM
My desired format is like this: col A+col B -> sheet 1
col A+col C -> sheet 2
col A+col D -> sheet 3, and so on...

Col A is the customer list, so I need to copy it to each new sheet.
There is around 160 columns in my spreadsheet, I'm a bit worried
and what's the fastest way to loop until last used column and create
160 new sheets?

The sheet names will be the value of first row per each column.

Any ideas?

Thanks.

xld
06-22-2005, 06:23 AM
My desired format is like this: col A+col B -> sheet 1
col A+col C -> sheet 2
col A+col D -> sheet 3, and so on...

Dim oThis As Worksheet
Dim oWs As Worksheet
Dim iLastCol As Long
Dim i As Long

Set oThis = ActiveSheet
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To iLastCol
Set oWs = Worksheets.Add(after:=Worksheets(Worksheets.Count))
oThis.Columns("A").Copy oWs.Columns("A")
oThis.Columns(i).Copy oWs.Columns("B")
Next i
oThis.Activate

Dreamer
06-22-2005, 06:34 AM
Thx! Could you please tell me how to name those sheets as well? ^^

xld
06-22-2005, 08:43 AM
Dim oThis As Worksheet
Dim oWs As Worksheet
Dim iLastCol As Long
Dim i As Long

Set oThis = ActiveSheet
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To iLastCol
Set oWs = Worksheets.Add(after:=Worksheets(Worksheets.Count))
oWs.Name = "mySheet" & i
oThis.Columns("A").Copy oWs.Columns("A")
oThis.Columns(i).Copy oWs.Columns("B")
Next i
oThis.Activate

sheeeng
06-22-2005, 07:07 PM
Dim oThis As Worksheet
Dim oWs As Worksheet
Dim iLastCol As Long
Dim i As Long

Set oThis = ActiveSheet
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To iLastCol
Set oWs = Worksheets.Add(after:=Worksheets(Worksheets.Count))
oWs.Name = "mySheet" & i
oThis.Columns("A").Copy oWs.Columns("A")
oThis.Columns(i).Copy oWs.Columns("B")
Next i
oThis.Activate



You can also enter manually your desired name for each sheet. Thanks :beerchug: , xld for the code. I'm just edit one line on the input box. Try it. I works for me. :thumb


Dim oThis As Worksheet
Dim oWs As Worksheet
Dim iLastCol As Long
Dim i As Long

Set oThis = ActiveSheet
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To iLastCol
Set oWs = Worksheets.Add(after:=Worksheets(Worksheets.Count))

oWs.Name = InputBox("Enter sheet name:", "Name for Sheet") 'Enter your desired name for sheets

'oWs.Name = "mySheet" & i
oThis.Columns("A").Copy oWs.Columns("A")
oThis.Columns(i).Copy oWs.Columns("B")
Next i
oThis.Activate


Thanks. :friends: