Solved

Help with Copy and Paste Script

Posted on 2010-11-18
8
342 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
  • 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 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