Hi,

I have asked this problem before and I wanted guidance on which site would
be having the info I need.

Dave P directed me to Chip and David M's site but Im lost .....

I have a macro by the name Macro1.

This macro basically takes data from a sheet called "Raw Data" and does some
consolidation of it and enters its results in othe worksheets.

I have pasted the code for Macro1 at the end of my signature.

I want that if any data in the range B:AG changes in "Raw Data" worksheet
changes due to actual data typing or due to pasting from another workbook
then when raw data worksheet is decativated Macro 1 may run.

In "my version" of VBA (crude) language I have put it below.

Private Sub Worksheet_Deactivate()

If Worksheet_Change = True then
Call Macro1
End if

End Sub

I understand that syntax is fully wrong but I dont know how to put it
correctly, please guide me.

Regards,
Hari
India



Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/20/2004 by Hari P
'

'
Dim rowcount As Integer
Dim ok As Integer
Dim p As Integer



Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("Consolidation of Raw data").Select
Range("b3:f65536").Select
Selection.ClearContents
Range("b3:p65536").Select
Selection.ClearContents
Range("d3").Select

Sheets("Raw Data").Select
Range("e3").Select
ok = 0
rowcount = 0
Do While ok = 0
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ok = 1
Else
rowcount = rowcount + 1
End If
Loop

rowcount = rowcount + 3

Range("j3").Formula = "=k3&TEXT(l3,"" dd-mmm-yy"")&TEXT(m3,"" dd-mmm-yy"")"
Range("j3").Select
Selection.AutoFill Destination:=Range("j3:j" & rowcount)
Columns("j:j").EntireColumn.AutoFit

Range("t3").Formula = "=u3&TEXT(v3,"" dd-mmm-yy"")&TEXT(w3,"" dd-mmm-yy"")"
Range("t3").Select
Selection.AutoFill Destination:=Range("t3:t" & rowcount)
Columns("T:T").EntireColumn.AutoFit

Range("B3:w" & rowcount).Select
Selection.Copy
Range("a3").Select
Sheets("Intermediate 1").Visible = True
Sheets("Intermediate 1").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("j2:j" & rowcount - 1).Select
Application.CutCopyMode = False
Selection.Cut
Range("e2").Select
Selection.Insert Shift:=xlToRight




Range("T2:T" & rowcount - 1).Select
Application.CutCopyMode = False
Selection.Cut
Range("f2").Select
Selection.Insert Shift:=xlToRight

Range("AZ2").Formula = "=MAX(L2,M2,v2,w2)"
Range("AZ2").Select
Selection.AutoFill Destination:=Range("AZ2:AZ" & rowcount - 1)

Rows("2:" & rowcount - 1).Select
Selection.Sort Key1:=Range("v2"), Order1:=xlDescending,
Key2:=Range("n2") _
, Order2:=xlDescending, Key3:=Range("m2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

Selection.Sort Key1:=Range("AZ2"), Order1:=xlDescending,
Key2:=Range("w2" _
), Order2:=xlDescending, Key3:=Range("v2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

Range("e2").Select
Range("e1:f" & rowcount - 1).AdvancedFilter Action:=xlFilterInPlace,
Unique:=True

Range("B2:f" & rowcount - 1).Select
Selection.Copy


Sheets("Consolidation of Raw data").Select


Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("AT2").Value = rowcount

Sheets("Intermediate 1").Select
ActiveSheet.ShowAllData
Rows("2:65536").Select
Selection.Clear
Range("B2").Select
Sheets("Intermediate 1").Visible = xlVeryHidden
Sheets("Consolidation of Raw data").Select

Range("f3").Select
ok = 0
rowcount = 0
Do While ok = 0
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then

If ActiveCell.Offset(0, -1).Value = "" Then

ok = 1

End If

End If

rowcount = rowcount + 1

Loop


rowcount = rowcount + 3

Range("aX2").Value = rowcount - 1


Range("aT3:aU65536").Select
Selection.ClearContents

Range("AT3").Formula = "=IF(b3<"""",b3,"""")"

Range("g3").Formula = "=IF(ISERROR(VLOOKUP($E3,INDIRECT(""'Raw
Data'!$j$3:$l$""&$AT$2),3,FALSE)),"""",VLOOKUP($E3 ,INDIRECT(""'Raw
Data'!$j$3:$l$""&$AT$2),3,FALSE))"
Range("h3").Formula = "=IF(ISERROR(VLOOKUP($E3,INDIRECT(""'Raw
Data'!$j$3:$m$""&$AT$2),4,FALSE)),"""",VLOOKUP($E3 ,INDIRECT(""'Raw
Data'!$j$3:$m$""&$AT$2),4,FALSE))"
Range("i3").Formula = "=IF(ISERROR(VLOOKUP($F3,INDIRECT(""'Raw
Data'!$T$3:$v$""&$AT$2),3,FALSE)),"""",VLOOKUP($F3 ,INDIRECT(""'Raw
Data'!$T$3:$v$""&$AT$2),3,FALSE))"
Range("j3").Formula = "=IF(ISERROR(VLOOKUP($F3,INDIRECT(""'Raw
Data'!$T$3:$w$""&$AT$2),4,FALSE)),"""",VLOOKUP($F3 ,INDIRECT(""'Raw
Data'!$T$3:$w$""&$AT$2),4,FALSE))"
Range("k3").Formula = "=IF(COUNTIF(INDIRECT(""'Raw
Data'!$j$3:$j$""&$AT$2),E3)=0,"""",COUNTIF(INDIREC T(""'Raw
Data'!$j$3:$j$""&$AT$2),E3))"
Range("l3").Formula = "=IF(K3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$j$3:$j$""&$AT$2)=E3),--(INDIRECT(""'Raw
Data'!$n$3:$n$""&$AT$2)=""Yes"")))"
Range("m3").Formula = "=IF(K3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$j$3:$j$""&$AT$2)=E3),--(INDIRECT(""'Raw
Data'!$s$3:$s$""&$AT$2)=""Pass"")))"
Range("n3").Formula = "=IF(COUNTIF(INDIRECT(""'Raw
Data'!$T$3:$T$""&$AT$2),F3)=0,"""",COUNTIF(INDIREC T(""'Raw
Data'!$T$3:$T$""&$AT$2),F3))"
Range("o3").Formula = "=IF(N3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$T$3:$T$""&$AT$2)=F3),--(INDIRECT(""'Raw
Data'!$X$3:$X$""&$AT$2)=""Yes"")))"
Range("p3").Formula = "=IF(N3="""","""",SUMPRODUCT(--(INDIRECT(""'Raw
Data'!$T$3:$T$""&$AT$2)=F3),--(INDIRECT(""'Raw
Data'!$Ag$3:$Ag$""&$AT$2)=""Pass"")))"


If rowcount < 3 Then
Range("g3:p3").Select
Selection.AutoFill Destination:=Range("g3:p" & rowcount)
Range("AT4").Formula =
"=IF(COUNTIF($AT$3:$AT3,b4)=0,IF(b4="""","""",b4), """")"

If rowcount = 5 Then
Range("AT4").Select
Selection.AutoFill Destination:=Range("AT4:AT" & rowcount - 1)
End If

End If

Range("ay2").Formula = "=COUNTIF(INDIRECT(""AU3:AU""&AX2),"""")"
Range("aw2").Formula = "=ax2-ay2"

Range("at3:at" & rowcount - 1).Select
Selection.Copy
Range("au3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Selection.Sort Key1:=Range("AU3"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("a3").Select

p = Sheets("Consolidation of Raw data").Cells(2, 49)
Sheets("Discrete data").Select

Range("b3:b65536").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Range("B3:b" & p).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=jobreqn.no."
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With



Range("A3").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub