Conditionally copy and paste the data with the help of highlighted cells

search for highlighted cell In each row
if there is highlighted cell in a row   and after the highlighted cell if there is data then copy the data that is after the highlighted cell  and paste to sheet2
if there is no highlighted cell in a row then simply copy  all the data and paste to sheet2
Avinash SinghAsked:
Who is Participating?
Bill PrewCommented:
Been working on this on and off, give it a try.

Sub DoCopy()
    ' Reference source and destination worksheets
    Set wsA = ThisWorkbook.Worksheets("Sheet1")
    Set wsB = ThisWorkbook.Worksheets("Sheet2")

    Application.ScreenUpdating = False
    ' Clear desination sheet, copy over header row and column
    wsA.Cells.Rows("1:1").Copy Destination:=wsB.Cells.Rows("1:1")
    wsA.Cells.Columns("A:A").Copy Destination:=wsB.Cells.Columns("A:A")
    ' Determine rows to process in dource sheet
    rowLast = wsA.Cells(wsA.Rows.Count, 1).End(xlUp).Row
    ' Process each source sheet row one by one
    For rowNum = 2 To rowLast Step 1
        ' Get start and end columns of used cells on this row
        colFirst = 2
        colLast = wsA.Cells(rowNum, Columns.Count).End(xlToLeft).Column
        ' Work right to left through used cells, looking for first cell with background color
        For colNum = colLast To 2 Step -1
            If wsA.Cells(rowNum, colNum).Interior.Pattern <> xlNone Then
                ' Found a filled cell, copy all cells after it
                colFirst = colNum + 1
                Exit For
            End If
        ' Copy cells after filled cell, or all cells if no filled cell found
        wsA.Range(wsA.Cells(rowNum, colFirst), wsA.Cells(rowNum, colLast)).Copy Destination:=wsB.Range(wsB.Cells(rowNum, 2), wsB.Cells(rowNum, colLast - colFirst + 2))

    Application.ScreenUpdating = True

End Sub

Open in new window

Ejgil HedegaardCommented:
Avinash SinghAuthor Commented:
solved by  Bill Prew and Ejgil Hedegaard thnx both of u for giving ur precious time for this post thnx alot
Bill PrewCommented:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.