Solved

Help with Copy and Paste Script

Posted on 2010-11-18
8
340 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Conditional Median Problem 1 15
VBA Help 18 44
Excel file that does not ask to be saved before exiting 6 20
Rather Simple Formatting Question 6 23
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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.
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…

770 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