Excel VBA code question

I have code that in certain rows, selects cells E:J and pastes them on another sheet. Now, I also need to copy the contents of N (skipping data in other cells), and paste it farther away on the other sheet.

In summary: For certain rows, copy E:J and paste on other sheet at G:O, and also copy N and paste on other sheet at P.

Is there a painless way to do this without running through the code twice? Thanks!
Set rng = wsEWork.Range("E7:E" & lrow)
    For Each item In rng
        'was it selected with an X in column E?
        If UCase(item.Value) = "R" Then      'row was selected
            Range("E" & item.row & ":J" & item.row).Copy
            'transfer to Install Checklist
            wsInstall.Range("E" & nextECopy).PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            nextECopy = nextECopy + 1
        End If
        wsEWork.Activate
    Next item

Open in new window

nbozzyAsked:
Who is Participating?

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

x
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.

nbozzyAuthor Commented:
Sorry -- code is correct, my description was wrong. I am pasting E:J into other sheet's E:J, but then first sheet's col N goes into other sheet's col P.
0
dlmilleCommented:
You merely need to add the  following in your loop:

            wsInstall.Range("N" & nextECopy).value = Range("P" & item.row)

Dave
See below



Set rng = wsEWork.Range("E7:E" & lrow)
    For Each item In rng
        'was it selected with an X in column E?
        If UCase(item.Value) = "R" Then      'row was selected
            Range("E" & item.row & ":J" & item.row).Copy
            'transfer to Install Checklist
            wsInstall.Range("E" & nextECopy).PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            wsInstall.Range("N" & nextECopy).value = Range("P" & item.row)
            nextECopy = nextECopy + 1
        End If
        wsEWork.Activate
    Next item

Open in new window

0
dlmilleCommented:
To optimize - no need for copy/paste - just direct value assignments in this case (runs much faster):
Set rng = wsEWork.Range("E7:E" & lrow)
    For Each item In rng
        'was it selected with an X in column E?
        If UCase(item.Value) = "R" Then      'row was selected
            'transfer to Install Checklist
            wsInstall.Range("E" & nextECopy & ":J" & item.row).value = Range("E" & item.row & ":J" & item.row)
            wsInstall.Range("N" & nextECopy).value = Range("P" & item.row)
            nextECopy = nextECopy + 1
        End If
        wsEWork.Activate
    Next item

Open in new window

Enjoy!

Dave
0
dlmilleCommented:
Apologies - I just tested - need the .value on the right side (of the equals) for the source range assigment.

Dave
Set rng = wsEWork.Range("E7:E" & lrow)
    For Each item In rng
        'was it selected with an X in column E?
        If UCase(item.Value) = "R" Then      'row was selected
            'transfer to Install Checklist
            wsInstall.Range("E" & nextECopy & ":J" & item.row).value = Range("E" & item.row & ":J" & item.row).Value
            wsInstall.Range("N" & nextECopy).value = Range("P" & item.row).Value
            nextECopy = nextECopy + 1
        End If
        wsEWork.Activate
    Next item

Open in new window

0

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
nbozzyAuthor Commented:
Dave, a million thank-you's for solving this and also for showing me a slick new trick!
0
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
Microsoft Excel

From novice to tech pro — start learning today.