SUB MACRO_RUN

Call neha1
Call neha2
Call neha4
Call neha5
Call neha6

End Sub

Function neha1()

For B = 2 To 50
For a = 3 To 50

If Worksheets("Activity").Cells(B, 4).Value =
Worksheets("IP_MPLS").Cells(a, 1).Value Then
Worksheets("IP_MPLS").Cells(a, 32).Value =
Worksheets("Activity").Cells(B, 10).Value
Worksheets("IP_MPLS").Cells(a, 21).Value =
Worksheets("Activity").Cells(B, 1).Value
Worksheets("IP_MPLS").Cells(a, 22).Value =
Worksheets("IP_MPLS").Cells(a, 22).Value & ". ;" &
Worksheets("Activity").Cells(B, 2).Value & ";" &
Worksheets("Activity").Cells(B, 8).Value
End If


Next a
Next B

End Function
Function neha2()

B = 2
For B = 2 To 50
For a = 3 To 100

If Worksheets("Activity").Cells(B, 4).Value =
Worksheets("BB").Cells(a, 1).Value Then
Worksheets("BB").Cells(a, 32).Value =
Worksheets("Activity").Cells(B, 10).Value
Worksheets("BB").Cells(a, 21).Value =
Worksheets("Activity").Cells(B, 1).Value
Worksheets("bb").Cells(a, 22).Value =
Worksheets("BB").Cells(a, 22).Value & ". ;" &
Worksheets("Activity").Cells(B, 2).Value & ";" &
Worksheets("Activity").Cells(B, 8).Value

End If
Next a
Next B

End Function

Function neha4()

B = 2
For B = 2 To 50
For a = 3 To 100

If Worksheets("Activity").Cells(B, 4).Value =
Worksheets("DGE").Cells(a, 1).Value Then
Worksheets("DGE").Cells(a, 32).Value =
Worksheets("Activity").Cells(B, 10).Value
Worksheets("DGE").Cells(a, 21).Value =
Worksheets("Activity").Cells(B, 1).Value
Worksheets("DGE").Cells(a, 22).Value =
Worksheets("DGE").Cells(a, 22).Value & ". ;" &
Worksheets("Activity").Cells(B, 2).Value & ";" &
Worksheets("Activity").Cells(B, 8).Value

End If
Next a
Next B

End Function



Function neha5()

B = 2
For B = 2 To 50
For a = 3 To 100

If Worksheets("Activity").Cells(B, 4).Value =
Worksheets("IPLC VCIPLC").Cells(a, 1).Value Then
Worksheets("IPLC VCIPLC").Cells(a, 32).Value =
Worksheets("Activity").Cells(B, 10).Value
Worksheets("IPLC VCIPLC").Cells(a, 21).Value =
Worksheets("Activity").Cells(B, 1).Value
Worksheets("IPLC VCIPLC").Cells(a, 22).Value =
Worksheets("IPLC VCIPLC").Cells(a, 22).Value & ". ;" &
Worksheets("Activity").Cells(B, 2).Value & ";" &
Worksheets("Activity").Cells(B, 8).Value

End If
Next a
Next B

End Function


Function neha6()

B = 2
For B = 2 To 50
For a = 3 To 150

If Worksheets("Activity").Cells(B, 4).Value =
Worksheets("Voice").Cells(a, 1).Value Then
Worksheets("Voice").Cells(a, 31).Value =
Worksheets("Activity").Cells(B, 10).Value
Worksheets("Voice").Cells(a, 10).Value =
Worksheets("Activity").Cells(B, 1).Value
Worksheets("VOICE").Cells(a, 21).Value =
Worksheets("VOICE").Cells(a, 22).Value & ". ;" &
Worksheets("Activity").Cells(B, 2).Value & ";" &
Worksheets("Activity").Cells(B, 8).Value

End If
Next a
Next B

End Function
===================================
All I am doing with this code is

Auto update of Column J (Activity sheet) into column V of
Voice, BB, IPLC VCIPLC, IP_MPLS and BB sheet. (Replacement of the
content).
Auto update of Column H (Activity sheet) into column AF of
Voice, BB, IPLC VCIPLC, IP_MPLS and BB sheet. (Additon to the existing
cell content in the format as todaydate;update).

Now code written is ineffieicinet and takes lot of time.
Please can anyone work on this and advise changes.Please take this
important

Hi sanju,

In Excel 2007 I have created the code below.
Since I do not have any testing data I hoop this will work.
Be sure to test it on a copy of your Excel File!

' -- start of code

Option Explicit

Dim shtSource As Worksheet

Sub Caller()
Set shtSource = Worksheets("Activity")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
nehaAll "IP_MPLS"
nehaAll "BB"
nehaAll "DGE"
nehaAll "IPLC VCIPLC"
nehaAll "Voice"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Set shtSource = Nothing
End Sub


Sub nehaAll(strTarget As String)
Dim shtTarget As Worksheet
Dim A As Integer
Dim B As Integer

Set shtTarget = Worksheets(strTarget)
For B = 2 To 50
For A = 3 To 50
If shtSource.Cells(B, 4).Value = shtTarget.Cells(A, 1).Value
Then
shtTarget.Cells(A, 32).Value = shtSource.Cells(B, 10).Value
shtTarget.Cells(A, 21).Value = shtSource.Cells(B, 1).Value
shtTarget.Cells(A, 22).Value = shtTarget.Cells(A, 22).Value &
_
". ;" & shtSource.Cells(B, 2).Value & ";" & _
shtSource.Cells(B, 8).Value
End If
Next A
Next B

Set shtTarget = Nothing
End Sub


'-- end of code

HTH,

Wouter

On Mar 10, 1:50*am, Wouter HM wrote:
Hi sanju,

In Excel 2007 I have created the code below.
Since I do not have any testing data I hoop this will work.
Be sure to test it on a copy of your Excel File!

' -- start of code

Option Explicit

Dim shtSource As Worksheet

Sub Caller()
* Set shtSource = Worksheets("Activity")
* Application.ScreenUpdating = False
* Application.Calculation = xlCalculationManual
* nehaAll "IP_MPLS"
* nehaAll "BB"
* nehaAll "DGE"
* nehaAll "IPLC VCIPLC"
* nehaAll "Voice"
* Application.ScreenUpdating = True
* Application.Calculation = xlCalculationAutomatic
* Set shtSource = Nothing
End Sub

Sub nehaAll(strTarget As String)
* Dim shtTarget As Worksheet
* Dim A As Integer
* Dim B As Integer

* Set shtTarget = Worksheets(strTarget)
* For B = 2 To 50
* * For A = 3 To 50
* * * If shtSource.Cells(B, 4).Value = shtTarget.Cells(A, 1).Value
Then
* * * * shtTarget.Cells(A, 32).Value = shtSource.Cells(B, 10).Value
* * * * shtTarget.Cells(A, 21).Value = shtSource.Cells(B, 1).Value
* * * * shtTarget.Cells(A, 22).Value = shtTarget.Cells(A, 22).Value &
_
* * * * * ". * * * * *;" & shtSource.Cells(B, 2).Value & ";" & _
* * * * * shtSource.Cells(B, 8).Value
* * * End If
* * Next A
* Next B

* Set shtTarget = Nothing
End Sub

'-- end of code

HTH,

Wouter

Tks Wouter for your promt reply. But this code is not working out in
EXCEL-2003. Getting error @ If shtSource.Cells(B, 4).Value =
shtTarget.Cells(A, 1).Value Then
My main concern is about logic used. I donot want to restrict the
search to 50 rows of destination worksheet. It should be till the last
row of the sheet.

Please help.
regars

Hi sanju,

I started my old computer with Excel 2003.
I copied the code from my message and dit not get an error.

I modified the code to your last request with dynamic rows
When you copy the code into Excel VBE be sure to combine any red line
with the line above it.

Option Explicit
Dim shtSource As Worksheet
Dim lngLastS As Long

Sub Caller()
Set shtSource = Worksheets("Activity")
lngLastS = shtSource.Cells(shtSource.Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
nehaAll "IP_MPLS"
nehaAll "BB"
nehaAll "DGE"
nehaAll "IPLC VCIPLC"
nehaAll "Voice"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Set shtSource = Nothing
End Sub

Sub nehaAll(strTarget As String)
Dim shtTarget As Worksheet
Dim lngLoopT As Long
Dim lngLoopS As Long
Dim lngLastT As Long
Set shtTarget = Worksheets(strTarget)
lngLastT = shtTarget.Cells(shtTarget.Rows.Count, 1).End(xlUp).Row
For lngLoopS = 2 To lngLastS
For lngLoopT = 3 To lngLastT
Sheets("Activity").Cells(lngLoopS, 4).Select
If shtSource.Cells(lngLoopS, 4).Value =
shtTarget.Cells(lngLoopT, 1).Value Then
shtTarget.Cells(lngLoopT, 32).Value =
shtSource.Cells(lngLoopS, 10).Value
shtTarget.Cells(lngLoopT, 21).Value =
shtSource.Cells(lngLoopS, 1).Value
shtTarget.Cells(lngLoopT, 22).Value =
shtTarget.Cells(lngLoopT, 22).Value & _
". ;" & shtSource.Cells(lngLoopS, 2).Value & ";" &
_
shtSource.Cells(lngLoopS, 8).Value
End If
Next lngLoopT
Next lngLoopS
Set shtTarget = Nothing
End Sub

HTH,

Wouter.

On Mar 11, 12:50*am, Wouter HM wrote:
Hi sanju,

I started my old computer with Excel 2003.
I copied the code from my message and dit not get an error.

I modified the code to your last request with dynamic rows
When you copy the code into Excel VBE be sure to combine any red line
with the line above it.

Option Explicit
Dim shtSource As Worksheet
Dim lngLastS *As Long

Sub Caller()
* Set shtSource = Worksheets("Activity")
* lngLastS = shtSource.Cells(shtSource.Rows.Count, 4).End(xlUp).Row
* Application.ScreenUpdating = False
* Application.Calculation = xlCalculationManual
* nehaAll "IP_MPLS"
* nehaAll "BB"
* nehaAll "DGE"
* nehaAll "IPLC VCIPLC"
* nehaAll "Voice"
* Application.ScreenUpdating = True
* Application.Calculation = xlCalculationAutomatic
* Set shtSource = Nothing
End Sub

Sub nehaAll(strTarget As String)
* Dim shtTarget As Worksheet
* Dim lngLoopT As Long
* Dim lngLoopS As Long
* Dim lngLastT As Long
* Set shtTarget = Worksheets(strTarget)
* lngLastT = shtTarget.Cells(shtTarget.Rows.Count, 1).End(xlUp).Row
* For lngLoopS = 2 To lngLastS
* * For lngLoopT = 3 To lngLastT
* * * Sheets("Activity").Cells(lngLoopS, 4).Select
* * * If shtSource.Cells(lngLoopS, 4).Value =
shtTarget.Cells(lngLoopT, 1).Value Then
* * * * shtTarget.Cells(lngLoopT, 32).Value =
shtSource.Cells(lngLoopS, 10).Value
* * * * shtTarget.Cells(lngLoopT, 21).Value =
shtSource.Cells(lngLoopS, 1).Value
* * * * shtTarget.Cells(lngLoopT, 22).Value =
shtTarget.Cells(lngLoopT, 22).Value & _
* * * * * ". * * * * *;" & shtSource.Cells(lngLoopS, 2).Value & ";" &
_
* * * * * shtSource.Cells(lngLoopS, 8).Value
* * * End If
* * Next lngLoopT
* Next lngLoopS
* Set shtTarget = Nothing
End Sub

HTH,

Wouter.

Thanks Wouter, it was great. You are champ.
One more addition, if you can help out , I want new addition in the
column 22 in Target sheet in wrapper mode. i.e it should start in
line below with format as todays date ; update from source sheet.

Please help.

Hi sanju

I think you would like to have this for column 22:

shtTarget.Cells(lngLoopT, 22).Value = _
shtTarget.Cells(lngLoopT, 22).Value & _
vbNewLine & CStr(Date) & ". ;" & _
shtSource.Cells(lngLoopS, 2).Value & ";" & _
shtSource.Cells(lngLoopS, 8).Value

The constant vbNewLine will wrap the rest of the data to the next line
insite the cell.

HTH,

Wouter

On Mar 12, 9:57*pm, Wouter HM wrote:
Hi sanju

I think you would like to have this for column 22:

* * shtTarget.Cells(lngLoopT, 22).Value = _
* * shtTarget.Cells(lngLoopT, 22).Value & _
* * vbNewLine & CStr(Date) & ". ;" & _
* * shtSource.Cells(lngLoopS, 2).Value & ";" & _
* * shtSource.Cells(lngLoopS, 8).Value

The constant vbNewLine will wrap the rest of the data to the next line
insite the cell.

HTH,

Wouter

Dear Wouter,

That was a magic. Thanks for that. Sorry for being greedy but I think
you can help here.

Now I have one more worksheet called index.
Please can you advise how can I get update of column 21 and column 22,
column 32 of all target worksheets ( Voice, iplc vciplc, ip_mpls, bb,
dge) in index sheet by typing the order number which is in column 1
of all these target sheets. in column 1 row 3 onwards in index sheet.

Right now I am managing this by multiple excel formula =INDEX(BB!
AF:AF,MATCH(Index!C20, BB!A:A,0)) which is not scalable.

regards
sanjay

Sorry Sanjay,

It is not clear to me what you like to have at tis point.

Wouter

On Mar 17, 12:52*am, Wouter HM wrote:
Sorry Sanjay,

It is not clear to me what you like to have at tis point.

Wouter

-----------------------------------

Dear Wouter,

There are different worksheet and each worksheet contain some
information in different columns against order number in column 1 of
these worksheet.
So searching update of a particular order is tedioug job by doing
control F in each worksheet and finding the update.

What I need is a macro to make simpler to find updates of these
orders.

So I have a different blank worksheet, so if anyone is typing the
order number in column 1, macro should show in which worksheet it is
and also pull up useful updates in column 21, 22 of worksheets in this
new worksheet.

I hope I am clear now. If you need, I can send you the excel file.

regards
sanjay

Hi sanjay,

It is clear now, thanks for the extra info.

Add this macro on your index sheet, you may need to edit it a
little...

Private Sub Worksheet_Change(ByVal Target As Range)

If FindOrder("IP_MPLS", Target) Then Exit Sub
If FindOrder("BB", Target) Then Exit Sub
If FindOrder("DGE", Target) Then Exit Sub
If FindOrder("IPLC VCIPLC", Target) Then Exit Sub
If FindOrder("Voice", Target) Then Exit Sub

End Sub


Private Function FindOrder(Str As String, _
Target As Range) As Boolean
Dim bln As Boolean
Dim row As Variant
Dim Sht As Worksheet

Set Sht = Worksheets(Str)

On Local Error Resume Next
row = Application.WorksheetFunction.Match( _
Target.Value, Sht.Range("A:A"), 0)
If Err.Number 0 Then
FindOrder = False
Exit Function
End If

Application.EnableEvents = False
Target.Offset(0, 1).Value = Str
Target.Offset(0, 2).Value = Sht.Cells(row, 21).Value
Target.Offset(0, 3).Value = Sht.Cells(row, 22).Value
Application.EnableEvents = True

FindOrder = True
End Function


HTH,

Wouter