Macros in Excel

**shaili** · 11-23-2009, 02:38 AM

Hi,
I have to split an excel sheet into 3 sheets.
The split should happen column wise.
For example..if there are 10 columns in a sheet ,i have to split this sheet in three sheets.
The first sheet should contain first 3 columns,the second sheet must contain the next 3 columns and the last sheet should contain the remaining 4 columns.
I am supposed to do this using some code and not manually cut and paste the columns.
Please help me and send the code for this.
Thank you.

**JeanRage** · 11-23-2009, 03:13 AM

Hi,

Turn on your macro recorder ... and perform he task ...
The code will be written for you ...

HTH

**rwgrietveld** · 11-23-2009, 07:46 AM

I think this will help you

Sub CopyCol()

Dim LastCol As Long
Dim x As Long, WSCount
Dim ColStr(1 To 2) As String

  LastCol = Worksheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Column '10
  ColNums = Int(LastCol / 3) '3
  WSCount = Worksheets.Count
 
  For x = 1 To ColNums '1,2,3
    If x = ColNums Then 'Last may have more or less columns than 3
      ColStr(2) = String(1, LastCol + 64)
    Else
      ColStr(2) = String(1, 3 * x + 64)
    End If
      ColStr(1) = String(1, 3 * x - 2 + 64)
      Sheets.Add After:=Worksheets(WSCount + x - 1)
      Worksheets(WSCount + x).Name = ColStr(1) & "-" & ColStr(2)
      Worksheets("Sheet1").Columns(ColStr(1) & ":" & ColStr(2)).Copy
      Sheets(WSCount + x).Paste Destination:=Worksheets(WSCount + x).Range("A1")
  Next x
  Application.CutCopyMode = False
End Sub

Note that the Source data is on "Sheet1"
New Sheets will be added to the WB.