Without Command Button.

J

jayanthimurali

Board Regular
Joined
Oct 15, 2010
Messages
238
Hello,

I have the below programme.

Private Sub CommandButton1_Click()
R1 = 1
R2 = 1
R3 = 2
Worksheets(1).Activate
Do While ActiveSheet.Cells(R1, 1).Value <> ""
Name = ActiveSheet.Cells(R1, 1).Value
Value = ActiveSheet.Cells(R1, 2).Value
Worksheets(2).Activate
Do While ActiveSheet.Cells(R2, 1).Value <> ""
Name_1 = ActiveSheet.Cells(R2, 1).Value
Value_1 = ActiveSheet.Cells(R2, 2).Value
If Name = Name_1 Then
Worksheets(3).Activate
Do While ActiveSheet.Cells(R3, 1).Value <> ""
R3 = R3 + 1
Loop
ActiveSheet.Cells(R3, 1).Value = Name
ActiveSheet.Cells(R3, 2).Value = Value
ActiveSheet.Cells(R3, 3).Value = Value_1
End If
R2 = R2 + 1
Worksheets(2).Activate
Loop
Worksheets(1).Activate
R1 = R1 + 1
R2 = 1
Worksheets(3).Activate
ActiveSheet.Name = "Sheet3"
Loop
End Sub

1.I have many names in sheet1 and Sheet2 like below

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>ram</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>murali</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Jayanthi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>ravi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>gaya</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD></TR></TBODY></TABLE>

but when i run the above programme, Iam getting the answer as.
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>ram</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>3</TD></TR></TBODY></TABLE>

Iwant the answer as.

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>ram</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>murali</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Jayanthi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>ravi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>gaya</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1</TD></TR></TBODY></TABLE>

2. I want this without using the Command Box. i.e., the programme should run automatically when i paste the text in the sheet1 and sheet2

Thanks in Advance
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Hi Jayanthi,

Try the code below. You need to paste this code in sheet1 and sheet2 codes in VBA project.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

R1 = 1
R2 = 1
R3 = 2
Worksheets(1).Activate
Do While ActiveSheet.Cells(R1, 1).Value <> ""
    
    Name = Sheet1.Cells(R1, 1).Value
    Value = Sheet1.Cells(R1, 2).Value
    
    Worksheets(2).Activate
        Do While ActiveSheet.Cells(R2, 1).Value <> ""
        
            Name_1 = ActiveSheet.Cells(R2, 1).Value
            Value_1 = ActiveSheet.Cells(R2, 2).Value
    
            If Name = Name_1 Then
                Worksheets(3).Activate
    
                Do While ActiveSheet.Cells(R3, 1).Value <> ""
                     R3 = R3 + 1
                Loop
    
                ActiveSheet.Cells(R3, 1).Value = Name
                ActiveSheet.Cells(R3, 2).Value = Value
                ActiveSheet.Cells(R3, 3).Value = Value_1
    
            End If
            
            R2 = R2 + 1
            Worksheets(2).Activate
        Loop
    
    Worksheets(1).Activate
    
    R1 = R1 + 1
    R2 = 1
    
    Worksheets(3).Activate
    ActiveSheet.Name = "Sheet3"
    
    Worksheets(2).Activate
    ActiveSheet.Name = "Sheet2"
    
    
    Worksheets(1).Activate
    ActiveSheet.Name = "Sheet1"
    
Loop
End Sub
 
Upvote 0
Hi Ogo,

I did not get what you are trying to tell. the code is already on the first sheet, and i want to run the programme without the Command button on the third sheet.

And also the when i run the programme i need to get all the names which are in first and second sheet on to the third sheet which iam not getting.
 
Upvote 0
I tried the code, but it is not working because I have already the below code on the first and second sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
Application.EnableEvents = False
othersol
Application.EnableEvents = True
End If
End Sub
Sub othersol()
Dim d As Object, lr As Long, a, e
Application.EnableEvents = False
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
a = Cells(2, "A").Resize(lr)
If Not IsEmpty(a) Then
For Each e In a
d(e) = d(e) + 1
Next e
Range("B1", Range("B" & Rows.Count).End(xlUp)).ClearContents
With Cells(2, "B").Resize(d.Count, 2)
.Value = Application.Transpose(Array(d.keys, d.items))
.Sort Key1:=.Cells(2, 2), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Columns("A:A").Delete Shift:=xlToLeft
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi, I'm away from my pc, so I am not able to give you the exact code, but try this
Add one if block in the existing worksheet_change sub.
If target.column =1 then
' the code I gave goes here
End if
 
Upvote 0
Hi Ogo,

This is my programme on the first sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
Application.EnableEvents = False
othersol
Application.EnableEvents = True
End If
End Sub
_____________________________________________________________________
Sub othersol()
Dim d As Object, lr As Long, a, e
Application.EnableEvents = False
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
a = Cells(2, "A").Resize(lr)
If Not IsEmpty(a) Then
For Each e In a
d(e) = d(e) + 1
Next e
Range("B1", Range("B" & Rows.Count).End(xlUp)).ClearContents
With Cells(2, "B").Resize(d.Count, 2)
.Value = Application.Transpose(Array(d.keys, d.items))
.Sort Key1:=.Cells(2, 2), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Columns("A:A").Delete Shift:=xlToLeft
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
__________________________________________________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
R1 = 2
R2 = 2
R3 = 2
Worksheets(1).Activate
Do While ActiveSheet.Cells(R1, 1).Value <> ""
Name = ActiveSheet.Cells(R1, 1).Value
Value = ActiveSheet.Cells(R1, 2).Value
Worksheets(2).Activate
Do While ActiveSheet.Cells(R2, 1).Value <> ""
Name_1 = ActiveSheet.Cells(R2, 1).Value
Value_1 = ActiveSheet.Cells(R2, 2).Value
If Name = Name_1 Then
Worksheets(3).Activate
Do While ActiveSheet.Cells(R3, 1).Value <> ""
R3 = R3 + 1
Loop
ActiveSheet.Cells(R3, 1).Value = Name
ActiveSheet.Cells(R3, 2).Value = Value
ActiveSheet.Cells(R3, 3).Value = Value_1
End If
R2 = R2 + 1
Worksheets(2).Activate
Loop
Worksheets(1).Activate
R1 = R1 + 1
R2 = 1
Worksheets(3).Activate
ActiveSheet.Name = "Sheet3"

Worksheets(2).Activate
ActiveSheet.Name = "Sheet2"


Worksheets(1).Activate
ActiveSheet.Name = "Sheet1"
Loop
End If
End Sub

When i input some text it sorts and give me the answer it is fine.
but when i run the las programme it should should sort the first and second sheet and on the third sheet the answer should be.

<TABLE style="WIDTH: 162pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=216 x:str><COLGROUP><COL style="WIDTH: 54pt" span=3 width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=19 width=72>A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=72>B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=72>C</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>ram</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>2</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>jayanthi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>2</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>murali</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>3</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>ravi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right x:num>1</TD></TR></TBODY></TABLE>

but as of now iam getting an error message as

Ambigous name detected-Worsheet_change


Thanks in advance
 
Upvote 0
Hi,
please test below code..And let me know if it doesn't work..:)
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Not Intersect(Target, Columns("A")) Is Nothing Then
Application.EnableEvents = False
othersol
Application.EnableEvents = True
End If
If Target.Column = 1 Then
R1 = 2
R2 = 2
R3 = 2
Worksheets(1).Activate
Do While ActiveSheet.Cells(R1, 1).Value <> ""
Name = ActiveSheet.Cells(R1, 1).Value
Value = ActiveSheet.Cells(R1, 2).Value
Worksheets(2).Activate
Do While ActiveSheet.Cells(R2, 1).Value <> ""
Name_1 = ActiveSheet.Cells(R2, 1).Value
Value_1 = ActiveSheet.Cells(R2, 2).Value
If Name = Name_1 Then
Worksheets(3).Activate
Do While ActiveSheet.Cells(R3, 1).Value <> ""
R3 = R3 + 1
Loop
ActiveSheet.Cells(R3, 1).Value = Name
ActiveSheet.Cells(R3, 2).Value = Value
ActiveSheet.Cells(R3, 3).Value = Value_1
End If
R2 = R2 + 1
Worksheets(2).Activate
Loop
Worksheets(1).Activate
R1 = R1 + 1
R2 = 1
Worksheets(3).Activate
ActiveSheet.Name = "Sheet3"
Worksheets(2).Activate
ActiveSheet.Name = "Sheet2"

Worksheets(1).Activate
ActiveSheet.Name = "Sheet1"
Loop
End If
 
End Sub

Sub othersol()

Dim d As Object, lr As Long, a, e
Application.EnableEvents = False
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
a = Cells(2, "A").Resize(lr)
If Not IsEmpty(a) Then
For Each e In a
d(e) = d(e) + 1
Next e
Range("B1", Range("B" & Rows.Count).End(xlUp)).ClearContents
With Cells(2, "B").Resize(d.Count, 2)
.Value = Application.Transpose(Array(d.keys, d.items))
.Sort Key1:=.Cells(2, 2), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Columns("A:A").Delete Shift:=xlToLeft
End If
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Try this again,
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Column = 1 Then
R1 = 2
R2 = 2
R3 = 2
Worksheets(1).Activate
Do While ActiveSheet.Cells(R1, 1).Value <> ""
Name = ActiveSheet.Cells(R1, 1).Value
Value = ActiveSheet.Cells(R1, 2).Value
Worksheets(2).Activate
Do While ActiveSheet.Cells(R2, 1).Value <> ""
Name_1 = ActiveSheet.Cells(R2, 1).Value
Value_1 = ActiveSheet.Cells(R2, 2).Value
If Name = Name_1 Then
Worksheets(3).Activate
Do While ActiveSheet.Cells(R3, 1).Value <> ""
R3 = R3 + 1
Loop
ActiveSheet.Cells(R3, 1).Value = Name
ActiveSheet.Cells(R3, 2).Value = Value
ActiveSheet.Cells(R3, 3).Value = Value_1
End If
R2 = R2 + 1
Worksheets(2).Activate
Loop
Worksheets(1).Activate
R1 = R1 + 1
R2 = 1
Worksheets(3).Activate
ActiveSheet.Name = "Sheet3"
Worksheets(2).Activate
ActiveSheet.Name = "Sheet2"
Worksheets(1).Activate
ActiveSheet.Name = "Sheet1"
Loop
End If
If Not Intersect(Target, Columns("A")) Is Nothing Then
Application.EnableEvents = False
othersol
Application.EnableEvents = True
End If
End Sub
Sub othersol()
Dim d As Object, lr As Long, a, e
Application.EnableEvents = False
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
a = Cells(2, "A").Resize(lr)
If Not IsEmpty(a) Then
For Each e In a
d(e) = d(e) + 1
Next e
Range("B1", Range("B" & Rows.Count).End(xlUp)).ClearContents
With Cells(2, "B").Resize(d.Count, 2)
.Value = Application.Transpose(Array(d.keys, d.items))
.Sort Key1:=.Cells(2, 2), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Columns("A:A").Delete Shift:=xlToLeft
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top