Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Hi. We have a spreadsheet that's more than 27,000 rows long that we download weekly. The sheet gets sorted by the values in Column A, and then copied and pasted to a separate sheet for each separate value (eg - value of '012' goes to sheet titled '12', value of '017' goes to sheet named '17', etc.) These new sheets don't already exist, either, they're created in the cut and paste process. Is there some way to have a macro move the rows for each value to a new sheet and name the sheet whatever the cell value is? The original data is left intact. I thought I had seen something like this requested before, but was unable to find it in the search. Thanks in advance for your help. Let me know if I need to be more clear on anything.

    - Brett

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

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    It shouldn't be too hard to write such a macro. Can you tell us exactly what kind of values column A contains? Are they numeric, formatted as 000, or text values? What is their range?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Hans, Thanks for the quick reply!

    The cell property shows they are formatted as text. The various values in the cells are listed below. I can't guarantee there will never be fewer or more than there are now, but this is what they've been for a year. They're sort of like customer numbers (more along the lines of partners), so if we ever add or delete any, then the list would change. Oh, and there are 3 rows of Headers at the top of the sheet, but they could be taken out if necessary for the purpose of running the macro. Otherwise, the data begins on row 4. The header information is just a title with the name of the report and month, then a blank row, then the table headers such as 'Market', 'Account #', etc. in row 3
    012
    017
    020
    029
    032
    038
    071
    073
    075
    096
    100
    106
    129
    131
    152
    157
    161
    162
    163
    169
    175

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

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    I have attached a sample workbook to test the following code:

    <code>Sub SplitSheets()</code>
    <code> Dim shtSource As Worksheet</code>
    <code> Dim shtTarget As Worksheet</code>
    <code> Dim lngRow As Long</code>
    <code> Dim lngMaxRow As Long</code>
    <code> Dim strTarget As String</code>
    <code></code>
    <code> On Error GoTo ErrHandler</code>
    <code> Application.ScreenUpdating = False</code>
    <code></code>
    <code> ' Substitute correct name</code>
    <code> Set shtSource = Worksheets("Data")</code>
    <code></code>
    <code> ' Loop through populated rows, starting at row 4</code>
    <code> lngMaxRow = shtSource.Cells(65536, 1).End(xlUp).Row</code>
    <code> For lngRow = 4 To lngMaxRow</code>
    <code> ' Get entry in column A</code>
    <code> strTarget = shtSource.Cells(lngRow, 1).Value</code>
    <code> ' Set reference to target sheet</code>
    <code> Set shtTarget = Worksheets(strTarget)</code>
    <code> ' Copy row to target sheet</code>
    <code> shtSource.Rows(lngRow).Copy shtTarget.Range("A65536").End(xlUp).Offset(1, 0)</code>
    <code> Next lngRow</code>
    <code></code>
    <code>ExitHandler:</code>
    <code> ' Clean up</code>
    <code> Set shtTarget = Nothing</code>
    <code> shtSource.Activate</code>
    <code> Set shtSource = Nothing</code>
    <code> Application.ScreenUpdating = True</code>
    <code> Exit Sub</code>
    <code></code>
    <code>ErrHandler:</code>
    <code> Select Case Err</code>
    <code> Case 9</code>
    <code> ' Sheet not found, so create it</code>
    <code> Set shtTarget = Worksheets.Add(After:=Worksheets(Worksheets.Count) )</code>
    <code> shtTarget.Name = strTarget</code>
    <code> Resume Next</code>
    <code> Case Else</code>
    <code> ' Something went wrong</code>
    <code> MsgBox Err.Description & Err, vbExclamation</code>
    <code> Resume ExitHandler</code>
    <code> End Select</code>
    <code>End Sub</code>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Hans, this worked perfectly! Thanks so much for your help.

    Is it easy at this point to get the headers to copy into the sheets in the same code, or would I be best off just copying and pasting them in at this point? I didn't realize the headers had to copy over as well, or I sure would have been thoughtful enough to mention it in my original post. Sorry about that.

    Regardless, this saves so much time from what we used to go through.

    Thanks again!

    - Brett

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

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Do you want the three header rows of the data sheet to be copied to all the new ones, or does each new sheet need an individual header?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    I want the three header rows of the data sheet to be copied to all the new ones. No changes.

    Thank you,

    Brett

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

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Just add one line near the end of the code (addition in bold):

    Set shtTarget = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    shtTarget.Name = strTarget
    shtSource.Range("1:3").Copy shtTarget.Range("1:3")
    Resume Next

    Notes:
    - Cells A3 in the original data sheet must be filled, otherwise header rows will be overwritten.
    - If you want to test on the demo workbook I posted, right-click the button on the data sheet end then move it out of the way, otherwise it will be copied to all target sheets.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Hans,

    This worked perfectly and does exactly what we need it to do.

    Thank you for all of your help with this. You just saved us so much time and helped us to avoid the possibility of human error in this part of our work.

    - Brett

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Hi Hans,

    I have a very similar problem to the one in this thread. My issue is that we need a macro which will run through a worksheet and whenever the status is "approved" move the information from that row to a separate worksheet. We would want to manually run this macro from a button, so the location the information is moved to will change to the first blank row in sheet 2 each time. Can you help.

    The worksheet is somewhat complex and contains some confidential information, so I have attached a mock-up here.

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

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Here is such a macro:
    <pre>Sub MoveApproved()
    ' Constants - adapt to situation
    Const cstrSourceSheet = "Sheet1"
    Const cstrTargetSheet = "Sheet2"
    Const clngTestCol = 3

    ' Variable declarations
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceRow As Long
    Dim lngTargetRow As Long
    Dim lngMaxSourceRow As Long
    Dim lngTestCol As Long

    ' Set error handling
    On Error GoTo ErrHandler

    ' Set reference to worksheets
    Set wshSource = Worksheets(cstrSourceSheet)
    Set wshTarget = Worksheets(cstrTargetSheet)

    ' Find last used row in source and target sheets
    lngMaxSourceRow = wshSource.Range("A65536").End(xlUp).Row
    lngTargetRow = Worksheets("Sheet2").Range("A65536").End(xlUp).Row + 1

    ' Loop backwards to avoid problems when rows are moved
    For lngSourceRow = lngMaxSourceRow To 2 Step -1
    ' Test for "Approved"
    If LCase(wshSource.Cells(lngSourceRow, clngTestCol)) = "approved" Then
    ' Move row
    With wshSource.Rows(lngSourceRow)
    .Copy wshTarget.Rows(lngTargetRow)
    .Delete
    End With
    ' Increase target row for next time
    lngTargetRow = lngTargetRow + 1
    End If
    Next lngSourceRow

    ExitHandler:
    ' Release object variables
    Set wshTarget = Nothing
    Set wshSource = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </pre>

    The constants at the start must be adapted to your situation. If you wish, you can add code to sort the result. Mockup with macro attached.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split One Sheet into Several Sheets based on Col A (2000 SP3)

    Thank you so much Hans - the lounge comes through once again ... you are a champion

Posting Permissions

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