Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Spreading Data Into Differrent Files (Excel 2002)

    Hi,
    Need some helps in how to spreading attached excel sample
    data into 2 files base on Cell highlighted in RED
    eg:
    Johor Bahru.xls
    MBBMY in Kedah.xls

    Thanks
    Gan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Spreading Data Into Differrent Files (Excel 2002)

    This macro should do what you want. Modify the constant as needed.
    <code>
    Sub SplitData()
    ' Modify as needed
    ' Trailing backslash is required.
    Const strPath = "C:Excel"

    Dim wshCurrent As Worksheet
    Dim wbkTarget As Workbook

    Dim lngStartRow As Long
    Dim lngEndRow As Long

    ' Reference to active worksheet
    Set wshCurrent = ActiveSheet

    ' Start at cell B6
    lngStartRow = 6
    ' Loop until the end of the worksheet
    Do While lngStartRow < 65536
    ' Last row of current branch
    lngEndRow = wshCurrent.Cells(lngStartRow, 2).End(xlDown).Row
    ' Create new workbook
    Set wbkTarget = Workbooks.Add
    ' Copy data into new workbook
    wshCurrent.Range(lngStartRow & ":" & lngEndRow).Copy _
    Destination:=wbkTarget.Worksheets(1).Range("A1")
    ' Close and save new workbook
    wbkTarget.Close SaveChanges:=True, _
    Filename:=strPath & wshCurrent.Cells(lngStartRow, 2)
    ' Start of next branch
    lngStartRow = wshCurrent.Cells(lngEndRow, 2).End(xlDown).Row
    Loop

    Set wbkTarget = Nothing
    Set wshCurrent = Nothing
    End Sub</code>

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spreading Data Into Differrent Files (Excel 2002)

    Thanks so much..it works! im really touch for the speed of your respons thanks alots...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •