When I look at your sample data, the only difference between what you have on Sheet1 and what you want on Sheet2 is that the numbers in column L have changed, and on sheet 2 they are sequential - changing at every blank row.
Am I right - or have I missed something.
If I am right, then run this macro which copies "Sheet1" to "Sheet2" (an empty worksheet named "Sheet2" must be present in the same workbook), it then numbers data in groups, incrementing the number at each blank row:
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim intCount As Integer
'copy Sheet1 to Sheet2 - assumes Sheet2 exists
'set start of data
Set rngStart = Worksheets("Sheet2").Range("L2")
'find end of data
Set rngEnd = Worksheets("Sheet2") _
.Range("L" & CStr(Application.Rows.Count)).End(xlUp)
intCount = 1
'loop through the data in column L
For Each rngCell In Worksheets("Sheet2").Range(rngStart, rngEnd)
'test for blank row (no data in column A or column L
If rngCell.Offset(0, -11).Value = "" And rngCell.Value = "" Then
'blank - so increment counter
intCount = intCount + 1
'test if number in column L
If IsNumeric(rngCell.Text) Then
'is a number - so replace it with counter value
rngCell.Value = intCount
End SubAs Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'.
Note that any data on "Sheet2" will be overwritten without warning.
If the above is not right, then I have a number of questions:
1. In your post you say Sheet1 data have a blank rows after every two rows of data.
but the data you posted shows a blank row after four rows of data - please clarify.
2. On Sheet2, the first group is shown as 6 rows of data before the first blank row, then the next two groups are 4 rows each. If this is correct how do you determine whether data from Sheet1 is copied in groups of four rows or groups of six rows.
3. Which columns is the data in.
4. Do you want all columns up to column L copied, (A to K), or only certain columns.