I am working with a workbook with three main sheets in it:
1. Summary sheet
2. Raw data sheet
3. Area list
The main purpose of this workbook is to create a list of customer contracts that can be tracked and edited by the contract auditors. On the Area list all the areas the specific auditor is responsible for are marked with a "Yes" and the others with a "No". The raw data gets transferred to the Summary sheet by the following:
Sheets("Raw").Range("d2:d100000", "e2:f100000").Copy Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 1)
where column "D" is the area name and "E:F" the customer reference numbers. This, however copies all the areas to the Summary sheet and not only those the auditor is responsible for. I now need to approach this in one of two ways;
1. either only copy the required areas to the summary listing by setting a condition testing the Area list for a "Yes" and then only copy those lines from the raw data sheet, or
2. copy all lines to Summary as is done now and then conditionally delete all lines that belongs to the "No" group.
I have tried Union, Concatenate, VLookup, IF and a lot more and have scanned the Internet for all possible solutions, but no succes. My latest effort to conditionally delete the unwanted rows:
***********************************
Set Sh1 = Sheets("ServiceAreas")
Set Sh2 = Sheets("Summary")
Application.ScreenUpdating = False
For n1 = 1 To LastRowcheck
nextrow = Sh2.Cells(Rows.Count, "A").End(xlUp).Row + 1
If ActiveCell.FormulaR1C1 = "=VLOOKUP(B2,ServiceAreas!C:D,1,false)" = True Then
With Sh1
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
.AutoFilterMode = False
Set rng = .Range("A1", .Cells(lastrow, lastcol))
rng.AutoFilter field:=4, Criteria1:="No"
rng.Offset(1, 0).SpecialCells(1).Copy Sh2.Range("A" & nextrow)
rng.Offset(1, 0).EntireRow.Delete shift:=xlUp
.AutoFilterMode = False
End With
End If
*************************
How do I get this right, then?
Thanking you in advance.
McTock
1. Summary sheet
2. Raw data sheet
3. Area list
The main purpose of this workbook is to create a list of customer contracts that can be tracked and edited by the contract auditors. On the Area list all the areas the specific auditor is responsible for are marked with a "Yes" and the others with a "No". The raw data gets transferred to the Summary sheet by the following:
Sheets("Raw").Range("d2:d100000", "e2:f100000").Copy Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 1)
where column "D" is the area name and "E:F" the customer reference numbers. This, however copies all the areas to the Summary sheet and not only those the auditor is responsible for. I now need to approach this in one of two ways;
1. either only copy the required areas to the summary listing by setting a condition testing the Area list for a "Yes" and then only copy those lines from the raw data sheet, or
2. copy all lines to Summary as is done now and then conditionally delete all lines that belongs to the "No" group.
I have tried Union, Concatenate, VLookup, IF and a lot more and have scanned the Internet for all possible solutions, but no succes. My latest effort to conditionally delete the unwanted rows:
***********************************
Set Sh1 = Sheets("ServiceAreas")
Set Sh2 = Sheets("Summary")
Application.ScreenUpdating = False
For n1 = 1 To LastRowcheck
nextrow = Sh2.Cells(Rows.Count, "A").End(xlUp).Row + 1
If ActiveCell.FormulaR1C1 = "=VLOOKUP(B2,ServiceAreas!C:D,1,false)" = True Then
With Sh1
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
.AutoFilterMode = False
Set rng = .Range("A1", .Cells(lastrow, lastcol))
rng.AutoFilter field:=4, Criteria1:="No"
rng.Offset(1, 0).SpecialCells(1).Copy Sh2.Range("A" & nextrow)
rng.Offset(1, 0).EntireRow.Delete shift:=xlUp
.AutoFilterMode = False
End With
End If
*************************
How do I get this right, then?
Thanking you in advance.
McTock
Last edited:
