Macro to Move Specific Data to Another Worksheet

[jeannie v]

Hi Experts:

Is there a way I can build a Macro to do the following:

This is all One Workbook

Worksheet 1: The Columns are: Dept, Login,User Name, Location and Count of
Ob as noted below

Worksheet 3: Column H is a # that detects how many Obs are Pre-determined
for that location (Detroit) ....Example: Only 2 Obs per Agent in Detroit are
allowed(maximum), but there were 4 Obs performed on Worksheet 1 Data. So, I
want to log on:

Worksheet 2: All lines for Location (Detroit) that Exceed Column H on
Worksheet 3 (2 Obs)

So the result should be: Worksheet 2 should have all lines of data for all
locations from Worksheet 1 that EXCEED the # of Obs pre-determined for that
location on Worksheet 3.

I would appreciate any help you can provide.

 

[Roger Govier]

Hi Jeannie

You could do it by adding an extra column to Sheet1 called Over.
Assuming Sheet3 has location in column A and Number of obs in column H, then
in F2 of sheet1 enter
=IF(D2="","",E2>VLOOKUP(D2,Sheet3!A:H,8,0))

On sheet 1 highlight column A:F>Data>Filter>Autofilter
Use the dropdown on Column F to select TRUE and that will be a list of all
the locations that have exceeded their Obs.

If you did want them on a separate sheet, just mark the rows in the Filtered
lists>Copy>Paste to another sheet.