+ Reply to Thread
Results 1 to 13 of 13

Find, Conditional Select Rows, Copy and Paste to new sheet

  1. #1
    Registered User
    Join Date
    03-25-2010
    Location
    QC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    29

    Find, Conditional Select Rows, Copy and Paste to new sheet

    Hello,

    I am new in Excel VBA. So I will need yr. expertises and helps. Thanks in advance.

    Every day, I receive a bulk XLS file containing bulk invoices. The bulk invoice containing more than 500 invoices which we ship to different stores. I would like to extract individual invoice base on First Row VALUE (CUSTOMER NAME) which repeat for each individual invoice and base on the last row of the invoice which are 3 BLANK ROWS. The extracted data will be paste on NEW worksheet and then PRINT using a SPECIFIC PRINTER & SPECIFIC TRAY. The below is how is look likes

    A1
    CUSTOMER NAME
    DATA
    DATA
    DATA
    BLANK ROW
    BLANK ROW
    BLANK ROW
    CUSTOMER NAME
    DATA
    DATA
    DATA
    BLANK ROW
    BLANK ROW
    BLANK ROW

    This is urgent, so any input is very appreciated.

    Regards,
    Last edited by FN2010; 03-26-2010 at 12:34 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    The data starts in cell A1 or cell A2?

    Each set of data is always 4 rows?

    You want those 4 rows copied into a sheet and printed...the sheetname? Any particular cells?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-25-2010
    Location
    QC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    The data start at A1

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    This macro will copy the data to Sheet3 and print that sheet using your default printer.
    Please Login or Register  to view this content.

    You can't actually address printer trays in Excel, not that I know of. But you can have multiple printer setups to the same printer. One installation of the printer would have the default settings pointing to the trays you want specifically when that printer driver is used.

    Then you can choose that particular printer...or set it to be the default prior to running this macro.

    (suggested article)

  5. #5
    Registered User
    Join Date
    03-25-2010
    Location
    QC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    Hi JBeaucaire,

    Thanks for yr. inputs. The code is work partially only. See below for my explaination

    1. The data to be repeat is variable, what I know is the new invoice always start with the "CUSTOMER NAME" and end with 1 BLANK ROW (My mistake) then repeat again and again until the end.

    2. The data to be copy and print is range from A to S column and must be print within a page.

    Thanks very for yr. inputs.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    Here you go:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-25-2010
    Location
    QC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    Hi JBeaucaire,

    You are almost there, you code is very good but the selection still not select the last 3 rows of individual invoice. I may induced you in error??? Okay the last row to be selected, copy and print IS BEFORE AN EMPTY ROW AND EMPTY CELL OF COLLUMN "S".

    One other thing: The formatting to print in 1 page is not working, I still print on 3 pages. Can you made it fit into 1 page AND ON LANDSCAPE so all wordings are not small.

    Please don't stop. You are almost the end.

    Thanks very much.

  8. #8
    Registered User
    Join Date
    03-25-2010
    Location
    QC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    Hello,

    Any helps to finish this code???

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    the selection still not select the last 3 rows of individual invoice. I may induced you in error??? Okay the last row to be selected, copy and print IS BEFORE AN EMPTY ROW AND EMPTY CELL OF COLLUMN "S".
    Using all caps is tantamount to yelling at me, so let's not do that. I know what the words mean, I just have no clue what your sentences mean.

    We can add landscape printing easily enough.
    Please Login or Register  to view this content.

    With no real assistance on your part, I have to test this on my own made up data, and it works for me.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-25-2010
    Location
    QC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    Hi Jerry,

    I am sorry if I offenses you, but I worries that you don't understand my explanation.

    See the attachment file and you will understand why. The Rows.Count use the column A to count so data will not be copy and print if the Rows.End have 0 value. See yellow highlined

    I would like to use the column G to scan the last row value.

    I am sorry to bug you but this work must finish by tomorrow morning. I am new in Excel, please helps.

    Thanks
    Attached Files Attached Files

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    You don't have to worry about offending people if you stick to the topic and stay unemotional.

    Now that you posted an actual data sheet, it's crystal clear the problem. Next time, let's start with that sheet so we can get you there that much quicker.

    I *think* this gets you there:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-25-2010
    Location
    QC, Canada
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    Hi Jerry,

    Thanks for the last quote. It work perfectly. I will start next time with a xls file so it will be more easy to figure out.

    Thanks very much for yr helps.

    Best Regards,

    Francois N.

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find, Conditional Select Rows, Copy and Paste to new sheet

    Glad to help.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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