vba code - copy cells above when value changes

Question

I am a vba beginner and i am wondering how to go about the code. I have the id numbers in columns "C" and "H". The values in column "H" are changing every little. So it looks like the below:

I need to go through column "H"and if the value changes, I need to copy the rows from the column "c" and paste them in another Sheet. A new sheet is needed when the value changes. So, it loops through "H" comes across 88888 and as a result 3x12345 goes to another sheet, 3x 22222 goes to another, 2x33333 goes to another one and so on. The cells above the change need to be copied to another sheet...

C H

12345 77777
12345 77777
12345 77777
22222 88888
22222 88888
22222 88888
33333 99999
33333 99999

44444 99999
44444 99999

Surely, there is a way to do this. I hope this is clear. I'd be grateful for your help.

Answer 1

This should work, good luck!

Sub YouShouldHavePostedAnAttemptFirst
dim c as Range
dim CtRows  As Integer, SheetCtr as Integer
'Try to put your data on sheet 1 then create a new sheet so that it is the second sheet in the workbook. 

SheetCtr = 2

ctrows = application.counta (sheets(SheetWithDataOnIt).Range("h:h"))
for each c in range(cells(1,8),cellS(ctrows,8))
     c.offset(,-1). Copy Sheets(SheetCtr).Cells(rows.count, WhatEverColumnYouWant).end(xlup).offset(1,0)

     if c.offset(1,) <> c then
          Sheets.Add after:=Sheets(ActiveWorkbook.Sheets.Count)
          SheetCtr = SheetCtr + 1

     end if
next c

end sub