Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Seperate data (Excel 2002)

    Hi

    I have been here before I am embarrased to say.

    I have a worksheet with 13807 lines, I have reduced the attached file down to a few lines

    What I am trying to do is seperate data into new worksheets, I was kindly given a macro for another spreadsheet and I am trying to adapt it.

    It copies about five sheets then I get an error, the easieast way to explain this is if you run the macro.

    Many Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperate data (Excel 2002)

    The error is cause dby the fact that the macro is trying to name a sheet using illegal characters in the name. The following characters are not allowed:

    / * ? [ and ]
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Seperate data (Excel 2002)

    To expand on Pieterse's reply: the name **Engineering A/C Only** in D1217 contains * and / characters and hence cannot be used as a worksheet name. You must either change this name, or decide on a way to change it to make an allowed sheet name.

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate data (Excel 2002)

    Hi Hans

    I would serve the purpose if at every change in saleable item and the sheets named after the saleable item number.

    is this more feasable?

    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Seperate data (Excel 2002)

    Probably, but this means that each sheet may contain multiple customer names. Try this version of the macro on a copy of the workbook to see if the results are OK:

    Public Sub Separate()
    Dim oSrc As Worksheet, oTgt As Worksheet
    Dim lTgtRow As Long
    Dim oCpyStart As Range, oCpyRange As Range, oNxtCell As Range
    Application.ScreenUpdating = False
    Set oSrc = Worksheets("PC Sales Indirect")
    oSrc.Range("A1").Sort Key1:=Range("A1"), _
    Order1:=xlAscending, Header:=xlYes
    Set oCpyStart = oSrc.Range("A2")
    For Each oTgt In Worksheets
    If oTgt.Name <> "PC Sales Indirect" Then
    oTgt.Cells.Clear
    End If
    Next oTgt
    Do While oCpyStart.Value <> ""
    Set oTgt = Nothing
    Set oNxtCell = oCpyStart.Offset(1, 0)
    Do While oCpyStart.Value = oNxtCell.Value
    Set oNxtCell = oNxtCell.Offset(1, 0)
    Loop
    Set oCpyRange = Range(oCpyStart, oNxtCell.Offset(-1, 6))
    On Error Resume Next
    Set oTgt = Worksheets(oCpyStart.Value)
    On Error GoTo 0
    If oTgt Is Nothing Then
    Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oTgt.Name = oCpyStart.Value
    End If
    oSrc.Range("A1:g1").Copy Destination:=oTgt.Range("A1")
    oCpyRange.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset (1, 0)
    oTgt.Cells.EntireColumn.AutoFit
    Set oCpyStart = oNxtCell
    Loop
    oSrc.Activate
    Application.ScreenUpdating = True
    End Sub

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate data (Excel 2002)

    Hi Hans

    It worked like a dream I am so grateful, After testing someone asked for an extra column to be added to the data, but I managed to adapt your code to include colulmn H.

    Thank you very much.

    I suppose its too much to ask how this works so I can adapt it for future use.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Seperate data (Excel 2002)

    After the declarations and initial assignments, the instruction

    oSrc.Range("A1").Sort Key1:=Range("A1"), _
    Order1:=xlAscending, Header:=xlYes

    sorts the source worksheet on column A. This is necessary since we will distribute the data to different sheets based on the contents of column A. The line

    Set oCpyStart = oSrc.Range("A2")

    sets the first cell to look at. The lines

    For Each oTgt In Worksheets
    If oTgt.Name <> "PC Sales Indirect" Then
    oTgt.Cells.Clear
    End If
    Next oTgt

    clear all worksheets except the source sheet. We then let oCpyCell loop through the cells in column A until we meet an empty cell:

    Do While oCpyStart.Value <> ""

    We move oNxtCell down as long as it has the same value as oCpyStart:

    Set oNxtCell = oCpyStart.Offset(1, 0)
    Do While oCpyStart.Value = oNxtCell.Value
    Set oNxtCell = oNxtCell.Offset(1, 0)
    Loop

    At the end, oNxtCell is the cell below the last one that had the same value as oCpyStart. We set oCpyRange to the rectangular range from oCpyStart to the cell one row above oNxtCell in column G (offset 6 columns from column A; if you need to include more or less columns, adjust the offset accordingly).

    Set oCpyRange = Range(oCpyStart, oNxtCell.Offset(-1, 6))

    We test if there exists a worksheet with the value of oCpyStart as name:

    On Error Resume Next
    Set oTgt = Worksheets(oCpyStart.Value)
    On Error GoTo 0

    If not, a worksheet of that name is created:

    If oTgt Is Nothing Then
    Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oTgt.Name = oCpyStart.Value
    End If

    We copy the column headers to the target sheet (modify G if needed):

    oSrc.Range("A1:G1").Copy Destination:=oTgt.Range("A1")

    and copy oCpyRange below it:
    oCpyRange.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset (1, 0)

    We adjust the column widths:

    oTgt.Cells.EntireColumn.AutoFit

    And move to the next cell in column A for the next step:

    Set oCpyStart = oNxtCell
    Loop

    The rest is cleaning up.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate data (Excel 2002)

    Hi Hans

    Thank you for your time and patience it is much appreciated.

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperate data (Excel 2002)

    Hans,

    This works great in Excel 2003. I have a user who wants to do this same thing in Excel 2007. When I tried to use your code, it said I had to open a file that allows macros. So, I opened the workbook in Excel 2003 and it worked great. But, some of the "fancy" formatting she did in 07 didn't transfer. Is there a way to do this same thing in 07? And, hey, I'm thrilled I was able to find this code and change it enough to work with our file (just changed the references to the sheet names, but that's huge progress considering I am code illiterate).

    Thanks!

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Seperate data (Excel 2002)

    Hi Melanie,

    If you are working in Excel 2007 you can save a workbook in two forms: The standard 2007 format and a 2007 Macro Enabled format. In order for the macro to work in 2007, you need to save the appropriate workbook in the Macro Enabled format. To do this:
    - Click on the Office button on the top left corner of Excel
    - Click on the arrow next to the Save As command
    - Select Macro Enabled workbook

    The macro should function now in this format.

    Regarding the "fancy" formatting that did not transfer. Excel 2007 has many new formatting features that are not compatible with 2003. Usually when you save a 2007 workbook, it will show a warning informing you that certain content is incompatable with older versions of Excel. (Called the Compatibility Checker). This removes all the incompatible formatting.
    Regards,
    Rudi

Posting Permissions

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