Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with Copy and Paste Script

Posted on 2010-11-18
8
Medium Priority
?
354 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
Technology Partners: 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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

578 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