+ Reply to Thread
Results 1 to 11 of 11

Editing a Macro to include multiple rows and include text formatting for leading zeros

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Editing a Macro to include multiple rows and include text formatting for leading zeros

    I have been using this for a coupe of years and I have a question about editing it a little. First I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet. Thanks!

    Please Login or Register  to view this content.
    Last edited by missmea2005; 04-04-2014 at 09:51 AM.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    Could you please attached your workbook so we have something to work on?
    thanks
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

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

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    1) The code that is transfering data from one workbook to the other is using the .Value method, meaning only the "Text" is being transferred, no formulas or formatting. So it is already doing what you've described.

    2) To copy multiple rows of data you could possibly look into applying a FILTER to your data, copying all visible data and pasting into your template. There's not enough information here to suggest more specifically than that.
    _________________
    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!)

  4. #4
    Registered User
    Join Date
    08-27-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    @p24leclerc this is the spreadsheet I'm referring to.

    @JBeaucaire I have leading zeros in the information that I am trying to copy over and when it pastes into the next sheet it pastes it over as "values", which excludes the zeros, so should I add a fomatting component first? Also, the amount of rows changes by the lines allocated for each vendor, thery're variable so I need it to paste 1 or more cells according to vendor.
    Last edited by missmea2005; 04-04-2014 at 09:52 AM.

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

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    YEs, you will need to format the target columns or cells with a number format that includes a specific number of digits. This will insure the "value" displays with the leading zeros.

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

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    Here's a tweak that changes the 1line copy command into 2line copy commands so you can add more controls. IN this version it is pasting the value AND the numberformat:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-27-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    Thanks very much!! Would this filter have to be by a certain criteria? I just want it to copy the next worksheet when column "I" changes the value and copy column "E" & "U" for all of the values related to "I".

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

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    Post your new workbook with all the changes you've made so far with these prior questions. I'll see about adding some code to only copy the template once per unque Vendor code and include all the rows for that vendor.

    Can you make sure the columns you want included in the template from the raw data are absolutely clear? Thanks.

  9. #9
    Registered User
    Join Date
    08-27-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    Ok. I made a tab of what I want the finished product to look like. When there is a change in name I want it to copy the columns that I noted in the "Amazon" tab in those columns. Thanks for everything!
    Last edited by missmea2005; 04-04-2014 at 09:53 AM.

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

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    Try this:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-27-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: Editing a Macro to include multiple rows and include text formatting for leading zeros

    Thanks!!! Worked like a charm!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Editing an IF statement to include a value as a currency
    By happy_smiler1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2012, 06:01 AM
  2. Replies: 0
    Last Post: 08-17-2011, 10:12 AM
  3. Average Formula - Include zeros
    By arvin in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-07-2010, 02:09 PM
  4. Modifying the SMALL function to not include zeros?
    By thewrathful in forum Excel General
    Replies: 4
    Last Post: 12-09-2009, 04:46 PM
  5. [SOLVED] Average Function (include Blank Cells and Zeros)
    By candice.sy@gmail.com in forum Excel General
    Replies: 17
    Last Post: 06-27-2006, 08:40 AM

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