VBA to copy range if interior.color is grey till next grey row

[Akbarov]

Hello community,

I am trying to write VBA code which will copy data from non-grey cell in column C until first grey cell ( RGB 191,191,191 )
Then loop from where it left , to copy until next grey cell.
Row 1,2,3 are headers. So first copy-paste should be from row 4 to 13
Then continue from 13 to 20 and so on..

I tried to do as in following VBA. But I do something wrong..
When I debug:
lastrow = 173 ( which is correct )
endrow = 13 ( which is also correct )
startrow = <--- this is always 0 , I think problem is here or may be I do bigger mistakes.

I will appreciate if anyone can help me to solve this..

  Sub copyBYcolor
  Dim rownum As Long
   Dim colnum As Long
   Dim startrow As Long
   Dim endrow As Long
   Dim lastrow As Long
   rownum = 3
   colnum = 3
   lastrow = ActiveSheet.Range("C65536").End(xlUp).Row
   With ActiveSheet.Range("C3:C" & lastrow)


   For rownum = 3 To lastrow
    Do
       If .Cells(rownum, 3).Interior.Color = RGB(191, 191, 191) Then
          startrow = rownum
       End If

       rownum = rownum + 1


   If (rownum > lastrow) Then Exit For

   Loop Until .Cells(rownum, 3).Interior.Color = RGB(191, 191, 191)
   endrow = rownum
   rownum = rownum + 1

   ActiveSheet.Range(Cells(startrow, 2), Cells(endrow, 17)).Copy

   'Sheets("Result").Select
   'Range("A1").Select
   'Sheets("Result").PasteSpecial xlPasteValuesAndNumberFormats


   Next rownum
   End With
   End Sub

I also asked this question in different forum, so this is cross-post link: VBA to copy between 2 rows based on cell's interior color

 

[vcoolio]

I am trying to write VBA code which will copy data from non-grey cell in column C.........

Hello Akbarov,

Could you not just filter on the non coloured cells? A short code could do this for you.

Cheerio,
vcoolio.

 

[Akbarov]

Thanks for reply, but I need to copy paste each range to new workbook, I think it is only way to achieve this.