How to specify multiple separate cells for copy in VBA

[Ed Harris]

Hi,
I have this code originally given to me by ZOT in 2020 and since modified. But now instead of the resize I want to copy multiple separate cells all on the same row dependent on the (r, C) method used in "FirstCell" - somethng like the range I tried to make below but fails.
This must be really simple but I cannot make it work.
Hope you can help

Public Sub GetVariusData() 'searches for coloured cells and collects the cell address, date, wind spd to sheet 2
    Sheets("November").Select
    
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ' Find the last row of data
    
    For r = 2 To FinalRow  ' Loop through each row
        For C = 121 To 624 'data block
           
            ThisValue = Cells(r, C).Interior.Color
            FirstCell = Cells(r, C).Address
            If ThisValue = RGB(255, 255, 0) Then   ' Decide if to copy based on contents of the row
                'Cells(r, 31).Resize(1, 6).Copy    ' Copy the cells 31 to 31+5 to the clipboard

                'Range("r:487,r:503").Copy       'fails

                
                Sheets("Sheet2").Select ' Set Sheet2 as the current sheet
                NextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 ' Define a variable called nextrow from 2nd column
                Cells(NextRow, 1).Value = FirstCell ' Select the cell on the second column (just a single cell this time)
                Cells(NextRow, 2).Select            ' Paste the stuff we copied earlier
                ActiveSheet.Paste                   ' Set sheet 1 as the active sheet again (because of the loop)
                Sheets("November").Select
            End If
        Next C
    Next r
End Sub

 

[Artik]

First you need to answer the relevant question.
If you copy a non-contiguous range of cells, you paste it into the target cell and get a continuous range. For example, you copy cells from columns A, C, E, G and paste them into a cell in column AA. The pasted range is AA:AD. Are you sure you need this?

Artik