Advance Filter & Copy (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

Here is the second file

Reply | Quote

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 is valid for your question.

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hallo again

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

Sorry for any inconvenience.

Regards

Reply | Quote