Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advance Filter & Copy (Excel 2000)

    Hallo

    I need help from somebody with more brains than me, please.

    I will attach 2 files, one with name BPfilterComplete is where I want to be and BPfilterTest is where I am at this moment.

    When you click the Macro button on the Test file on the Data worksheet it will extract records for each unique BO number and past it in newly created worksheets.

    My problem is this:

    I want to copy the contents of sheet GJBP at the top of each newly created sheet. The Transaction Reference (JNL/08/001) must increase by one fon each new sheet, see workbook with name BPfilterComplete.

    I also want to copy the line in row 202 on the Data sheet (rangename NegTotal) just after the last row of extracted data on each newly created worksheet. The cell marked in blue must the add the total of the amounts as a negative total in the appropriate cell (no need for the cell to be blue when it is copied, I just add the colour for easy reference)

    When the information is extracted I also want the target columns the same size as the original from GJBP sheet.

    Any help or advise will be appreciated.
    Regards
    Kobus

  2. #2
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advance Filter & Copy (Excel 2000)

    Here is the second file
    Regards
    Kobus

  3. #3
    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: Advance Filter & Copy (Excel 2000)

    I would not separate them. Keeping them together makes more sense. you can use autofilter to view what each sheet would look like if desired.

    The advice I gave to a similar question in <post#=509611>post 509611</post#> is valid for your question.

    Steve

  4. #4
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advance Filter & Copy (Excel 2000)

    Hallo again

    I forgot to mention that I want to develop my macro code further to do this automatically.

    Sorry for any inconvenience.

    Regards
    Regards
    Kobus

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advance Filter & Copy (Excel 2000)

    Steve

    Thank you for the advice, but the reason I want it in the requested format is as follows:

    First the speadsheets go to accounts department for upload in the system and then each BO site must get a copy of only the information applicable to them, hence the need to separate the information.

    In my department I do keep all the info in one file as suggested.

    Regards
    Regards
    Kobus

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

    Re: Advance Filter & Copy (Excel 2000)

    Try this:

    Sub ExtractReps()
    Dim ws1 As Worksheet
    Dim wsNew As Worksheet
    Dim rng As Range
    Dim r As Long
    Dim n As Long
    Dim c As Range

    Set ws1 = Sheets("Data")
    Set rng = Range("Database")

    'extract a list of Sales Reps
    ws1.Columns("g:g").AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Range("I1"), Unique:=True
    r = Cells(Rows.Count, "I").End(xlUp).Row

    'set up Criteria Area
    Range("J1").Value = Range("g1").Value

    For Each c In Range("I2:I" & r)
    'add the rep name to the criteria area
    ws1.Range("J2").Value = c.Value
    'add new sheet and run advanced filter
    Sheets("GJBP").Copy After:=Worksheets(Worksheets.Count)
    Set wsNew = Worksheets(Worksheets.Count)
    wsNew.Name = c.Value
    wsNew.Range("C9") = "JNL/08/" & Format(c.Row - 1, "000")
    rng.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Data").Range("j1:j2"), _
    CopyToRange:=wsNew.Range("A14:G14"), _
    Unique:=False
    n = wsNew.Range("A65536").End(xlUp).Row + 1
    ws1.Rows(202).Copy Destination:=wsNew.Range("A" & n)
    wsNew.Range("E" & n).Formula = "=-SUM(E2:E" & (n - 1) & ")"
    Next c
    ws1.Select
    ws1.Columns("I:J").Delete
    End Sub

  7. #7
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advance Filter & Copy (Excel 2000)

    Hans

    Baie, baie dankie vir jou wonderlike werk.

    This is 100% what I needed, thank you very much.

    I am very sure that I speak for all who enjoy and benefit from the input of yourself, Steve, Rory, etc, etc, who put a lot of your time in to help us, when I say I can not emagine operating without this Forum.

    Thank you again
    Regards
    Kobus

Posting Permissions

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