none
How to Convert Columns in to separate workbook (I have a header for every column) RRS feed

  • Question

  • Dear Experts,

    I have a data in which my first column is DATE, other columns are data with header. I want to split the columns in different workbooks.

    Like first column will be data and then as per header first column. then other workbook date second data column. It will be awesome if workbook names will be header.

     Date
    Bat Cat Dot Ele
    1997-08-29
    5 8 8 7
    1997-09-01
    7 5 5 4
    1997-09-02
    4 2 4 8
    1997-09-03
    96 6 3 5

    I want Date + BAT Column in one workbook named as Bat, Second workbook Date+Cat column workbook named as Cat (VBA code will be good)

    Thanks in advance. 


    • Edited by nddie Wednesday, August 9, 2017 11:48 AM
    Wednesday, August 9, 2017 10:10 AM

All replies

  • nddie,
    re:  write me some code

    To help you get started...
    This code creates 3 new workbooks that are copies of the worksheet named "Sheet1")
    '---
    Sub ThisIsAnExample()
    Dim N As Long
    For N = 1 To 3
    ThisWorkbook.Worksheets("Sheet1").Copy
    Next
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)




    • Edited by James Cone Wednesday, August 9, 2017 1:00 PM
    Wednesday, August 9, 2017 12:34 PM
  • Thanks Jim, But above code creates same file three times not splitting columns as mentioned above. Please check

    Wednesday, August 9, 2017 12:48 PM
  • Hi nddie,

    Here is the code.

    Sub Test()

    Dim sourceWS As Worksheet

    Dim targetWB As Workbook

    Dim targetWS As Worksheet

    Path = ActiveWorkbook.Path

    Set sourceWS = ActiveSheet

    Application.ScreenUpdating = False

    For i = 2 To i = 5

    Set targetWB = Application.Workbooks.Add

    Set targetWS = targetWB.Worksheets(1)

    sourceWS.Columns(1).Copy targetWS.Columns(1)

    sourceWS.Columns(i).Copy targetWS.Columns(2)

    targetWB.SaveAs Path & "\" & sourceWS.Cells(1, i)

    targetWB.Close

    Next i

    Application.ScreenUpdating = True

    End Sub

    Best Regards,

    Terry

    Thursday, August 10, 2017 7:46 AM
  • Thanks Alot for your help. But above code not showing any result. I ran it on the same data sheet.

    Please recheck. the code. 

    Thanks in advance

    Thursday, August 10, 2017 9:03 AM
  • Hi nddie,

    Sorry for carelessness. Please replace

    For i = 2 To i = 5

    with

    For i = 2 To 5

    Thanks for understanding.

    Best Regards,

    Terry

    Friday, August 11, 2017 8:41 AM