Solved

Help with Copy and Paste Script

Posted on 2010-11-18
8
344 Views
Last Modified: 2012-05-10
I have a spread sheet that is being populated from another data base.  I am taking this data in the worksheet "Managed site list test" and deleting entries from Canada, calculating 8 weeks back from today, and then highlighting them yellow.  My next step is to search these highlighted fields based on their color and Column H and when I find one that meets both criteria I want to select the whole row, copy it and then paste it into the corresponding worksheets labeled "PI, Laser, OCC, DP".  For example  I want to copy the row from "Sheet 1" where the row is yellow and column H=PI and paste that into the worksheet titled "PI".

The issue I am having is that I can run the script with no errors but when I go to the other worksheets I don't see any data copied there.

I have attached my test data and script for review.

Mind you that this is my first forray into scripting and I'm sure the script I have written is messy by programing standards.  I have now been looking at this for 2 days and I can't for the life of me figure out what I did wrong.  When I take the IF THEN ELSE statements and put them into their own module with simulated data I get an error message as well but I don't see that when I run my orginal script, and I did make sure to call out my statement lines as well in the test.  Any help would be greatly appreceated.  
Managed-site-list-test.xls
0
Comment
Question by:jefjar
[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
  • 3
  • 2
8 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34163924
There are a lot of subroutines in your file. Can u mention the sequence of those as well? It would be easier for us to decipher the case.
0
 

Author Comment

by:jefjar
ID: 34165048
- The first routine connects to our database and pulls the information over to the Excel spreadsheet.  
- Then I sort the data by date in ascending order and then by product in ascending order
- I then delete any row that has a blank in the State field column C
- Then I look for any date, column I, that is 57 days, 8 weeks, less than todays date and highlight them.
- Then I want the script to run through the highlighted cells and look for the prouduct line PI, select the entire row and copy it.
- Then I want to activate the corresponding worksheet "PI" and then paste it into that sheet.  
- That needs to continue through all of the highlighted section adding each product line to the corresponding worksheets.  When it's complete I should have a list of all of the PI issues on one sheet etc....

Does that help?
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34165846
sorry jefjar... need one more input. Can u please add the subroutine names along with the narrations? Thanks.
0
Industry Leaders: 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!

 

Author Comment

by:jefjar
ID: 34167266
You need this in the code itself?
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34167295
No I mean in here

- The first routine connects to our database and pulls the information over to the Excel spreadsheet.  
- Then I sort the data by date in ascending order and then by product in ascending order
- I then delete any row that has a blank in the State field column C
- Then I look for any date, column I, that is 57 days, 8 weeks, less than todays date and highlight them.
- Then I want the script to run through the highlighted cells and look for the prouduct line PI, select the entire row and copy it.
- Then I want to activate the corresponding worksheet "PI" and then paste it into that sheet.  
- That needs to continue through all of the highlighted section adding each product line to the corresponding worksheets.  When it's complete I should have a list of all of the PI issues on one sheet etc....
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34169017
Hi, use this for your copy code section.

When you were pasting to A1, you were overwriting the row every time.  Also, I have used direct references to save activating and selecting cells and ranges.

Regards,

Rob.
While row_count <> last_row
    If Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Value = "PI" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("PI").Cells(Worksheets("PI").Cells(65536, 1).End(xlUp).Row + 1, 1)
    ElseIf Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Value = "LA" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("Laser").Cells(Worksheets("Laser").Cells(65536, 1).End(xlUp).Row + 1, 1)
    ElseIf Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Value = "OCC" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("OCC").Cells(Worksheets("OCC").Cells(65536, 1).End(xlUp).Row + 1, 1)
    ElseIf Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Range("A1").Cells(row_count, search_col).Value = "CP" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("DP").Cells(Worksheets("DP").Cells(65536, 1).End(xlUp).Row + 1, 1)
    End If
    row_count = row_count + 1
   Wend

Open in new window

0
 

Author Comment

by:jefjar
ID: 34172255
Rob, thanks for the help.  I've pasted this script into mine and run it.  But there is no data showing up on the other worksheets.  In your string I see that you have .Cells(65536,1).End(xlup).Row+1,1). Doesn't this start at the bottom of the worksheet?  Shouldn't this be .Cells(1,1)?  Sorry to ask stupid questions but like I said I'm an extreme newbie at writing script, as I'm sure you can tell.
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34178111
No, with these lines:
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("PI").Cells(Worksheets("PI").Cells(65536, 1).End(xlUp).Row + 1, 1)

What happens is, if the If condition is met (which is whether Sheet1 row_count search_col has the correct color, and whether Sheet1 row_count search_col value is PI), this lines runs.  So what it does is this:

>> Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy
Copies the Row number given by the matchine row_count to the clipboard.

>> Worksheets("PI").
It will copy the data to the PI worksheet (when you follow the .Copy with a parameter, this becomes the Paste destination)

>> Cells(Worksheets("PI").Cells(65536, 1).End(xlUp).Row + 1, 1)
This is in two parts.  The outer Cells() reference is given the *next* availabe row number, and column 1.  The inner cell reference of
Worksheets("PI").Cells(65536, 1).End(xlUp).Row + 1
returns the next empty row.  When you use Worksheets("PI").Cells(65536, 1).End(xlUp) it is the same as going to cell A65536, and pressing CTRL + Up.  That gives you the last used cell, so the + 1 simply gives you the next empty cell.  Therefore, the outer Cells() reference is given the next empty row number, and column 1, to paste the data to.

There might be a problem with identifying the correct row_count col_count cell though in the If statements.  I have taken out the Range("A1") bits.  I don't think they're needed.  Try this.

Regards,

Rob.
While row_count <> last_row
    If Worksheets("Sheet1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Cells(row_count, search_col).Value = "PI" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("PI").Cells(Worksheets("PI").Cells(65536, 1).End(xlUp).Row + 1, 1)
    ElseIf Worksheets("Sheet1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Cells(row_count, search_col).Value = "LA" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("Laser").Cells(Worksheets("Laser").Cells(65536, 1).End(xlUp).Row + 1, 1)
    ElseIf Worksheets("Sheet1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Cells(row_count, search_col).Value = "OCC" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("OCC").Cells(Worksheets("OCC").Cells(65536, 1).End(xlUp).Row + 1, 1)
    ElseIf Worksheets("Sheet1").Cells(row_count, search_col).Interior.ColorIndex = search_color And Worksheets("Sheet1").Cells(row_count, search_col).Value = "DP" Then
        Worksheets("Sheet1").Rows(row_count & ":" & row_count).Copy Worksheets("DP").Cells(Worksheets("DP").Cells(65536, 1).End(xlUp).Row + 1, 1)
    End If
    row_count = row_count + 1
Wend

Open in new window

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 …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

740 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