Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to put data on different tabs (2000)

    I have a spreadsheet that has a field called Association. Every time the Association changes to a different association, is it possible to put that Associations data on a different worksheet and so on and so forth? The spreadsheet is sorted by Association. Thanks for any help you can provide.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to put data on different tabs (2000)

    Could you provide a little more info?

    This could be done (what I understand) with a macro, but it would require a lot more info. COuld you provide a sample workbook, with a more detailed request?

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to put data on different tabs (2000)

    I am attaching an example of the workbook. Thanks for your help.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to put data on different tabs (2000)

    One more question. If there is already a worksheet for the association, do you want to add the data to what is already there or replace the data?
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to put data on different tabs (2000)

    There won't be but if there was, than replace the data. Is it possible to have the headings on each associations sheet? If not, that is OK.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to put data on different tabs (2000)

    Does this do what you want:

    <code>
    Public Sub SplitAssoc()
    Dim lLast As Long, I As Long, J As Long
    Dim oSrc As Worksheet, oTgt As Worksheet
    Application.ScreenUpdating = False
    Set oSrc = ActiveSheet
    lLast = oSrc.Range("A65536").End(xlUp).Row - 1
    For I = 1 To lLast
    If oSrc.Range("A1").Offset(I, 0).Value <> oSrc.Range("A1").Offset(I - 1, 0).Value Then
    If Not oTgt Is Nothing Then
    oTgt.Range("A1:IV1").EntireColumn.AutoFit
    End If
    Set oTgt = Nothing
    On Error Resume Next
    Set oTgt = Worksheets(oSrc.Range("A1").Offset(I, 0).Value)
    On Error GoTo 0
    If oTgt Is Nothing Then
    Set oTgt = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
    oTgt.Name = oSrc.Range("A1").Offset(I, 0).Value
    oSrc.Range("1:1").Copy
    oTgt.Paste Destination:=oTgt.Range("A1")
    Else
    oTgt.Cells.ClearContents
    oSrc.Range("1:1").Copy
    oTgt.Paste Destination:=oTgt.Range("A1")
    End If
    J = oTgt.Range("A65536").End(xlUp).Row
    End If
    oSrc.Range("A1").Offset(I, 0).EntireRow.Copy
    oTgt.Paste Destination:=oTgt.Range("A1").Offset(J, 0)
    J = J + 1
    Next I
    If Not oTgt Is Nothing Then
    oTgt.Range("A1:IV1").EntireColumn.AutoFit
    End If
    Application.CutCopyMode = False
    oSrc.Activate
    Application.ScreenUpdating = True
    End Sub
    </code>
    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to put data on different tabs (2000)

    Thanks Legare - that works perfectly. Thanks for all your help.

Posting Permissions

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