VBA insert row when value Changes

[kourbeh]

Hi guys
I hope someone can help me with this one!
I need to check the value of the Cell in B column, once it is changed I need to insert a new row, copy the contains of the first row to the new row, my code is not working properly
this is the sheet:
https://app.box.com/s/bntxmsw7wzbtonxrab57

Sub Insertrow()    SheetName = "Sheet1"
    RngStr = "B2:B" + CStr(Worksheets(SheetName).UsedRange.Rows.Count)
    Set Rng = Worksheets(SheetName).Range(RngStr)
    
    For Each Cell In Rng
        If Application.CountIf(Rng, Cell.Value) = 1 Then
            Cell.EntireRow.Insert
            Rows("1:1").Select
             Selection.Copy
             Range(Rng).Select
             ActiveSheet.Paste
                      
    
        End If
        Next Cell


End Sub

 

[Momentman]

Is what you posted in the screenshot the final output or what?

 

[kourbeh]

No it is not, it is the input, the macro I wrote isnot working, I don't know what is the reason

 

[Momentman]

You want to copy the header row to the newly inserted rows?? right? If thats what you want to do, then

Sub runmacro()
      For i = Range("A" & Rows.Count).End(xlUp).Row - 1 To 2 Step -1
         If Range("B" & i).Value <> Range("B" & i + 1).Value Then
            Rows(i + 1).Select
            Selection.Resize(1).Insert
            Rows(1).EntireRow.Copy Range("A" & i + 1)
          End If
    Next i
End Sub

 

[kourbeh]

Actually my macro couldn't create the line even

 

[Momentman]

Does the code do what you want?

 

[kourbeh]

nope

 

[Momentman]

So what does the macro do or not do? cos it works as i think it should

 

[kourbeh]

it is suppose to insert a row when he finds that the value of B2:B is changed!
but it is not working

 

[Momentman]

insert a new row, copy the contains of the first row to the new row, my code is not working properly

This is the output from my code, when i ran it on your data

Excel 2010
	A	B	C	D	E	F	G	H
1	QueryDate	City Name	Att	Succ	Blk	rbc	hsb	Tdom
2	23/06/2013	Montreal	1	2824	0	0.00	11	0.39
3	24/06/2013	Montreal	4	3857	0	0.00	16	0.72
4	25/06/2013	Montreal	7	3747	1	0.03	21	0.17
5	26/06/2013	Montreal	10	3746	0	0.00	17	0.45
6	27/06/2013	Montreal	13	3770	0	0.00	15	0.40
7	28/06/2013	Montreal	16	3886	0	0.00	16	0.41
8	QueryDate	City Name	Att	Succ	Blk	rbc	hsb	Tdom
9	23/06/2013	Toronto	2	274	0	0.00	2	0.41
10	24/06/2013	Toronto	5	648	0	0.00	2	0.31
11	25/06/2013	Toronto	8	643	0	0.00	2	0.34
12	26/06/2013	Toronto	11	538	0	0.00	6	1.10
13	27/06/2013	Toronto	14	619	0	0.00	9	1.43
14	28/06/2013	Toronto	17	579	0	0.00	4	0.69
15	QueryDate	City Name	Att	Succ	Blk	rbc	hsb	Tdom
16	23/06/2013	Vancouver	3	1780	0	0.00	3	0.56
17	24/06/2013	Vancouver	6	2082	0	0.00	7	0.31
18	25/06/2013	Vancouver	9	1626	0	0.00	3	0.18
19	26/06/2013	Vancouver	12	1735	0	0.00	8	0.46
20	27/06/2013	Vancouver	15	1675	0	0.00	4	0.24
21	28/06/2013	Vancouver	18	1831	0	0.00	7	0.38
Sheet1

The inserted rows are thee ones shown in Bold, the data from row 1 is copied in there. Isnt that what you want?