+ Reply to Thread
Results 1 to 8 of 8

In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input

  1. #1
    Registered User
    Join Date
    03-01-2012
    Location
    Clarksville, IN
    MS-Off Ver
    Excel 2007
    Posts
    9

    In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input

    Hi, I'm Carol and I'm brand new to the Forums.

    My position requires me to take variable number of samples from existing reports and analyze them for compliance with our standards. I get about 13 different reports each month, and I've gotten a lot of great information from this and other forums (mostly this one) on how to do most of what I need to do, such as: grab a random sample based on how many I need, copy the sample over to a new sheet (creating it if it doesn't already exist), and grabbing the corresponding cells of the sample items for the data points I need to analyze. This forum has been an absolute lifesaver, and I've been able to find most of what I need just by searching.

    My problem is automating the last part. Right now, I use the same module on all my reports that

    1. asks me how many items I want to pull from the active sheet
    2. asks me on what sheet do I want the results and
    3. pastes a random number of those unique ID #s from the original sheet to the target sheet with no change in the code.

    I also have it pull 3-4 corresponding cells from the original sheet that in the same row as each unique ID # and paste them on the target sheet, but I have to manually tell it which cells to compare and copy, and I have to manually tell it from which cells to get the headers for that data (for example, on one report the Unique ID # is an investigation #, then I have three criteria that I have to analyze "Accuracy" "Completeness" and "Documented") that sits among several other cells with info I don't need to look at and therefore don't need to copy from the original to the target sheet.

    So what I want to do is ask "How many headers will you need?"

    Take that variable (say it's 3) and then ask

    What do you want to name Header 1? And put Header 1 in to Cells (1, 2) on target sheet, then say I name it "Accuracy" and "Accuracy" is part of the header on the original sheet. I then what it to know that it's taking the corresponding data from the the row of unique ID # (call it 12345678) and the same column as the "Accuracy" header on that original sheet and move it to the target sheet.

    I then want it to ask "What do you want to name Header 2? and do the same thing with Cells (1, 3) on the target sheet.

    Here is my code for the manual part of that task

    Please Login or Register  to view this content.
    Any help is appreciated.

    Sincerely,

    Carol

  2. #2
    Forum Contributor
    Join Date
    02-08-2012
    Location
    South Suffolk
    MS-Off Ver
    Excel 2007
    Posts
    102

    Re: In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user i

    Welcome - you obviously enjoy the challenge of VBA! Rather than provide a complete solution which would take more time that I have currently, the following ideas may help you.
    The FIND method as applied to a range is a very good way of locating both the column headings and the unique IDs in the original sheet. You need to have a working range for the result and set that to something like = search_range.FIND(HeaderText) where the search_range is the header row in the original sheet.
    The resultant range will let you find the column (e.g. result_range.Column).
    Loop through your headers and you will know what columns are needed.
    If you are not picking up the data items at the same time as getting the random selection of IDs, you can get the code to walk down the IDs in the target sheet and look for them in the appropriate row in the original sheet - perform a FIND on that column and get the row from the result (result_range.ROW). Now you have the row and column needed to pick up the data.
    An alternative to having to ask for the row headings would be to type them into the first row of the target sheet and work across that until an empty cell.
    Hope this will inspire & good hunting.

  3. #3
    Registered User
    Join Date
    03-01-2012
    Location
    Clarksville, IN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user i

    Thank you - that has definitely gotten me thinking!

    I am thinking for the first part, I know what headers I'll need, and I just have to type in a word that matches one of the words in the headers (no header has the same phrase I believe) and grab it that way. I will have to do more research on the exact syntax, but yes, this does help, thank you again.

    Carol

  4. #4
    Forum Contributor
    Join Date
    02-08-2012
    Location
    South Suffolk
    MS-Off Ver
    Excel 2007
    Posts
    102

    Re: In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input

    You might find the following code useful - it performs a table look-up by searching the left-hand column and top row with values supplied and then returns the intersection.
    It isn't fully bomb-proof but should work under most circumstances. It can be used as a worksheet usser-defined function.
    Please Login or Register  to view this content.
    Hope this helps as a component in your code.
    Last edited by AndyPS; 03-07-2012 at 02:35 PM. Reason: code truncated

  5. #5
    Registered User
    Join Date
    03-01-2012
    Location
    Clarksville, IN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input

    Hi Andy,

    It's helping - I just need to figure out how the syntax works with each other. I can see the logic in it it, and I'm learning a lot.

    Thanks!

    Carol

  6. #6
    Registered User
    Join Date
    03-01-2012
    Location
    Clarksville, IN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input

    So, I'm still stuck unfortunately. I'm not as worried about finding the headers - I've found it would be easier if I just copied the entire row. But I keep getting hung up on this: because it's a random sample, I can't seem to get it to find the row the sample is in.

    I'm not sure if I'm making it more difficult than it has to be.

    I want to determine a new spreadsheet on which to post my sample, (say: test), I've got that down.

    I want to select a random number of items (values are unique identification numbers within the spreadsheet, such as 3456789) based on user input, (say: 25), that too, works.

    I want to, once the macro has selected i of 25 items, copy and paste the row that corresponds with the randomly chosen unique id number onto the new worksheet tab.

    I am not able to do that. Right now the code is for selecting certain cells based on the randomly selected unique id and copy/pasting them, but it's too much to alter the macro for every different type of spreadsheet that I have to pull samples from. I need to streamline this and I'm just not getting it.

    dummydata.xls

    The dummy data may not completely match what's in the code as far as the cells I'm copying, but that's the problem, I have to change those values for each workbook and it's not saving time. If I just copy the rows of the corresponding selected value to the new worksheet, that will save me a lot of time.

    Any help would be appreciated.


    Code:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-01-2012
    Location
    Clarksville, IN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input

    I have tried for 4 solid hours to get this to work. Unfortunately I'm not getting anywhere. I can copy and paste my sampled unique ids to a second created worksheet, but as far as trying to get the corresponding row to copy and paste I'm practically in tears tearing my hair out.

    I've looked at several different threads here, I've tried recording a Macro and reverse engineering, but the fact that I have to iterate through the selected variables out of my column and go back to copy the row that variable came from, constantly throws me.

    Please Login or Register  to view this content.
    As you can see I've tried a number of things (all commented out).

    I'm re-teaching myself VBA, and I know that a lot of the core syntax I'm not getting, as I'm trying to get what I need to do my job, but also learn from it.

    Any help is appreciated. In the meantime, I'll keep searching.

    Thanks

  8. #8
    Registered User
    Join Date
    03-01-2012
    Location
    Clarksville, IN
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: In copy/pasting cells to a new sheet cannot automate cells (1, x) based on user input

    Okay, I've cleared one hurdle, I have the copy/paste of the row fixed. It needs fine tuning still, and I'll probably be back for more help (sorry ) but here is the completed code (commented for hopeful ease-of-reading).

    I couldn't have done any of this without the help of this forum, you all are wonderful!



    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1