Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA code question

Posted on 2011-02-22
5
Medium Priority
?
168 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

609 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