+ Reply to Thread
Results 1 to 19 of 19

Creating a macro that duplicates content depending on a number in column A

  1. #1
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Question Creating a macro that duplicates content depending on a number in column A

    Is there a way to generate a macro that could duplicate a row(its content) based on a number in the first column?

    For example:
    I have an excel file, please see the attached image. In column "A" I want the macro to duplicate the same exact row based on what the number is on its first cell. In the attached image line 11 youll see number 2 in the "A" cell, i want to be able to have 2 same exact rows with the same content. If for example Line 11 had 3 it will be 3 lines duplicating the same content. Can a macro do that? If so can you please provide me a sample on how to do it?

    Thanks
    Attached Images Attached Images

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    Welcome to the Forum!

    By macro do you mean a worksheet formula or VBA code solution?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Creating a macro that duplicates content depending on a number in column A

    Quote Originally Posted by Leith Ross View Post
    Hello webuxer,

    Welcome to the Forum!

    By macro do you mean a worksheet formula or VBA code solution?
    Hi, Thanks
    I believe its going to be a VBA solution, I normally get an excel file every week and i need to format it, the excel file its different every week, the columns are the same but the content changes. For example this week the excel could have 100 lines, maybe next week 200. What do you suggest? im new to programing in excel.

    Thanks

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    I can you you a better answer if you post a copy of the workbook.

    To Attach a File:
    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  5. #5
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Creating a macro that duplicates content depending on a number in column A

    Ok i uploaded the .xls file, its just a sample. If you notice on the excel file like 3 column "A" has number "3" instead of having one row, i want to distribute this line inserting 3 same exact rows. Please see the attached image, i highlighted the change. I normally do this manually, but it takes time if i have to do this when there is 10 lines to duplicate.

    Thanks
    Attached Images Attached Images
    Attached Files Attached Files

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    Thanks for posting the working book. I see now what you need. This would be best handled with VBA. Do the results need to be on the same sheet or could they placed on another sheet?

  7. #7
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Creating a macro that duplicates content depending on a number in column A

    Quote Originally Posted by Leith Ross View Post
    Hello webuxer,

    Thanks for posting the working book. I see now what you need. This would be best handled with VBA. Do the results need to be on the same sheet or could they placed on another sheet?
    if possible on the same sheet.
    Thank you very much, for your help, also one more thing. in column B with VBA code can we remove the dash to only show the 5 digit number?

    Thanks Again

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    Sure, that's no problem.

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    Once the new rows are added, does the value in column "A" become 1 for the new rows?

  10. #10
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Creating a macro that duplicates content depending on a number in column A

    Quote Originally Posted by Leith Ross View Post
    Hello webuxer,

    Once the new rows are added, does the value in column "A" become 1 for the new rows?
    Yes
    Thanks

  11. #11
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Creating a macro that duplicates content depending on a number in column A

    Try this out - run it with that worksheet Active

    Please Login or Register  to view this content.
    Last edited by GeneralDisarray; 09-15-2011 at 02:07 PM.

  12. #12
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Creating a macro that duplicates content depending on a number in column A

    here is the sheet i ran that on (your sample submission cleaned with the macro).
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Creating a macro that duplicates content depending on a number in column A

    Quote Originally Posted by GeneralDisarray View Post
    Try this out - run it with that worksheet Active

    Please Login or Register  to view this content.
    sorry dumb question, where do i paste the code into?

  14. #14
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Creating a macro that duplicates content depending on a number in column A

    Hmm... I just noticed you are using the previous version - so i'm assuming you can't open what i attached. if you can open it, just use that.

    Also, when i pasted in that code the first time one important line got "commented out" -- copy the code from my edited post (below pasted again).

    To make a macro in excel '03,
    1.On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor.
    2.On the Insert menu, click Module.
    3.Type or copy your code into the code window of the module.
    4.If you want to run the macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5.
    5.When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu.


    Please Login or Register  to view this content.

  15. #15
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    This macro has been added to the attached workbook. A button has been added to the sheet to run it. If you have any questions, please ask.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Creating a macro that duplicates content depending on a number in column A

    Quote Originally Posted by Leith Ross View Post
    Hello webuxer,

    This macro has been added to the attached workbook. A button has been added to the sheet to run it. If you have any questions, please ask.
    Please Login or Register  to view this content.
    Thank you so much, one more quick question, how can i have this macro available in all future excel workbooks?

  17. #17
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    You could add it to your Personal.xlsx workbook. The sheet name would have to be "Sheet1" and the data located in columns "A:B" in the workbooks the macro would run on.

  18. #18
    Registered User
    Join Date
    09-15-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Creating a macro that duplicates content depending on a number in column A

    Quote Originally Posted by Leith Ross View Post
    Hello webuxer,

    You could add it to your Personal.xlsx workbook. The sheet name would have to be "Sheet1" and the data located in columns "A:B" in the workbooks the macro would run on.
    Is there a way to modify the code so when the macro is ran, after distributing the content, instead of having in column a all "1", have it to show 1,2,,3,4,5 etc.. until the last cell that has content. In the excel provided I believe its up to 140 lines, so it will be 1 to 140 in the "A" column.

    Thanks for your help

  19. #19
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Creating a macro that duplicates content depending on a number in column A

    Hello webuxer,

    This should put the correct count in place of the repeating "1". The change is in bold text.

    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)

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