Solved

Excel VBA code question

Posted on 2011-02-22
5
165 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:nbozzy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:nbozzy
ID: 34951544
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34957454
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34957462
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34957476
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
 

Author Comment

by:nbozzy
ID: 34962421
Dave, a million thank-you's for solving this and also for showing me a slick new trick!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question