Solved

Help with Copy and Paste Script

Posted on 2010-11-18
8
339 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
 

Author Comment

by:jefjar
ID: 34167266
You need this in the code itself?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now