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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Bill PrewIT / Software Engineering ConsultantCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PrewIT / Software Engineering ConsultantCommented:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.