Comparing cell values

[jshynes]

I have 3 workbooks that come to me everyday from different people, each workbook has only on worksheet. 1 worksheet is called AHT, 1 is called effective use, and the other is called daily statistics. I need to copy certain values in the effective use and AHT worksheets to the daily statistics worksheet. The AHT worksheets has about 20 rows and the effective use has about 600 rows. The common cell/value column between these 2 worksheets is called the empid. What I need is to create, is a macro that will sequential compare the empid's between the 2 worksheets (AHT and effective use), if they match they copy certain cells from AHT and effective use to daily statistics. If they don't match, it then needs to advance to the next cell in effective use compare it with AHT, match? then copy, don't match? then advance to the next cell in effective use.
continue the comparison thru all 600 cells in effective use, once done then advance to the next empid in AHT and then repeat the process. If you could point me in the right direction I would certainly appreciate it.
unfortunately i tried to install colo's utility into my workbook but I get the following error message:"could not load an object because it is not available on this machine".
Thanks in advance

 

[Damon Ostrander]

Hi js,

Your problem is fairly straightforward, but in order to give you code that does this we need to know the columns containing the empid on the two sheets, which columns of data need to be copied to the third sheet, and where on that sheet? In addition, are the empid data on the two sheets in ascending order? This is important to the design of the code.

Damon

 

[jshynes]

Thank you for the reply: Here's the information you requested.
On the AHT worksheet has the following ranges that need to be copied to the Daily Statistics worksheet:
Empid B8 to Daily Stats cell A3
Name C8 to Daily Stats cell B3
NCH D8 to Daily Stats cell C3
ATT G8 to Daily Stats cell D3
AHT H8 to Daily Stats cell E3
Wrap L8 to Daily Stats cell F3
Hold P8 to Daily Stats cell G3
' the number rows above can fluctuate

The Effective Use worksheet Empid is in the range of D5:D600;
The following ranges need to copied to Daily Statistics worksheet
Daily Transfer E5 to Daily stats cell H3
Daily Transfer Perc F5 to Daily Stats cell I3
Weekly Transfer G5 to Daily Stats cell J3
Weekly Transfer Perc H5 to Daily Stats cell K3
Monthly Transfer I5 to Daily Stats cell L3
Monthly Transfer Perc J5 to Daily Stats cell M3
'the number of rows can fluctuate

 

[jshynes]

Sorry, forgot to tell you that the empids are not in sequential order, and the data for the effective use is actually in a workbook called effective use in a worksheet called "TPA".

 

[Damon Ostrander]

Hi again JS,

There are still a couple things that are unclear to me:

1. When you refer for example to "Stats cell A3" do you mean the first available (empty) cell in column A, or do you really mean to overwrite the data that was previously there?

2. What is the name of the worksheet in workbook AHT (or are they the same)?

3. What does "Perc" refer to? Is this the worksheet name I just asked about?

4. You mentioned that empids are not in sequential order in Effective Use. Is this the case with AHT as well?

Damon

 

[jshynes]

Here's the answers to your questions.

1. In the daily stats worksheet I've got a macro that will clear out the data in the cells before any copying will take place. So, essentially all cells will be empty and there won't be any data to overwrite. So, what mean to copy data to cell A3, A3 will be clear

2. The worksheet and workbook are one in the same, both called AHT. This workbook has only one worksheet in it.

3. Perc, is short for percentage and is the title of the column of where the data is stored. i.e "daily transfer percentage".

4. The empids in AHT are not in numeric or sequential order as the same in Effective Use. On the AHT worksheet they are in the range A5:A20.

 

[Damon Ostrander]

Hi JS,

Here is a macro that I believe does what you describe. You can put this macro in any workbook. The only requirement is that all three of the workbooks you describe must be open in Excel when you run the macro. The macro should be put in a standard macro module (keyboard Alt-TMV, then Alt-IM to create a new macro module, paste code in code pane). The macro assumes that there is an empid somewhere in Effective Use that matches each empid in the AHT table. Here's the macro:

Sub WriteDailyStats()

Dim AHTtable As Range
Dim EUtable As Range
Dim DailyStats As Worksheet
Dim DSTrow As Long
Dim AHTrow As Long
Dim EUrow As Long
Dim iCol As Integer

Set AHTtable = Workbooks("AHT.xls").Sheets("AHT").[d5:j20]
With Workbooks("Effective Use.xls").Worksheets("TPA")
Set EUtable = .Range(.[d5], .[j65536].End(xlUp))
End With
Set DailyStats = Workbooks("Daily Statistics.xls").Worksheets("Daily Stats")
DSTrow = 3 'Starting row in DST worksheet

For AHTrow = 1 To AHTtable.Rows.Count
EUrow = Application.Match(AHTtable.Cells(AHTrow, 1), EUtable.Columns(1), 0)
With DailyStats
For iCol = 1 To 6
.Cells(DSTrow, iCol + 7) = EUtable(EUrow, iCol + 1)
Next iCol
DSTrow = DSTrow + 1
End With
Next AHTrow

End Sub

 

[jshynes]

Thanks, for the help. Sorry it's taken awhile for me to get the macro to work in the worksheets. I've had to make some modifications, it works to the point were it will copy the data from the effective use worksheet only. Nothing is copied from the AHT worksheet. How do I modify it so that it will copy certain cells from the AHT worksheet to the dailystatstemplate worksheet?

Here's is the code as it stands now.
Sub BuildWksht()
'
' BuildWksht Macro
' Macro recorded 9/3/2002 by JHynes
'

'
ChDir "C:Team Stats"
Workbooks.Open Filename:="C:Team StatsEffective Use.xls"
Sheets("TPA").Select
Workbooks.Open Filename:="C:Team StatsAHT.xls"

Dim AHTtable As Range
Dim EUtable As Range
Dim DailyStats As Worksheet
Dim DSTrow As Long
Dim AHTrow As Long
Dim EUrow As Long
Dim iCol As Integer


Set AHTtable = Workbooks("AHT.xls").Sheets("ASR1.2.asp Excel=True&ReportGro").[b817]
With Workbooks("Effective Use.xls").Worksheets("TPA")
Set EUtable = .Range(.[d5], .[j65536].End(xlUp))
End With
Set DailyStats = Workbooks("DailyStatsTemplate.xls").Worksheets("Daily Stats")
DSTrow = 3 'Starting row in DST worksheet

For AHTrow = 1 To AHTtable.Rows.Count
EUrow = Application.Match(AHTtable.Cells(AHTrow, 1), EUtable.Columns(1), 0)
With DailyStats
For iCol = 1 To 6
.Cells(DSTrow, iCol + 7) = EUtable(EUrow, iCol + 1)
Next iCol
DSTrow = DSTrow + 1
End With
Next AHTrow

Thanks again for all your help