+ Reply to Thread
Results 1 to 14 of 14

duplicate range n times and insert at blank row

  1. #1
    Registered User
    Join Date
    01-08-2008
    Location
    Fort Worth, Tx
    MS-Off Ver
    2016
    Posts
    19

    duplicate range n times and insert at blank row

    I have a range that display information about peoples activity. I want to copy and insert at the first blank row the same range n times based on the number of months i want to see (n).

    Here's an brief example

    Bob Reno, NV 8
    Sue Paris, TX 2
    Kim LA, CA 7

    If i input "3" it should look like
    Bob Reno, NV 8
    Sue Paris, TX 2
    Kim LA, CA 7
    Bob Reno, NV 8
    Sue Paris, TX 2
    Kim LA, CA 7
    Bob Reno, NV 8
    Sue Paris, TX 2
    Kim LA, CA 7

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: duplicate range n times and insert at blank row

    Hi, maybe you can work with something like this:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: duplicate range n times and insert at blank row

    No need for loop...
    Please Login or Register  to view this content.
    Last edited by sintek; 05-23-2020 at 07:14 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    01-08-2008
    Location
    Fort Worth, Tx
    MS-Off Ver
    2016
    Posts
    19

    Re: duplicate range n times and insert at blank row

    so i am a VBA novice. I have a cell already on another worksheet that is filled in by the user indicating how many months they want to see (how many duplicates). Do i need to replace that with the input box? The selected range varies but is the only thing on a sheet, as the results of the previous sheets input. can the range be everything on that sheet? I can attach a sample if you like

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: duplicate range n times and insert at blank row

    I can attach a sample if you like
    Yes, please and explain your expected requirement...via this sample file...

  6. #6
    Registered User
    Join Date
    01-08-2008
    Location
    Fort Worth, Tx
    MS-Off Ver
    2016
    Posts
    19

    Re: duplicate range n times and insert at blank row

    On 'personnel' i put a 'y' or an 'n' to include them in the report.The page 'Mid-Atlantic' is the one that needs to have the blank rows created by an 'n' removed.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-08-2008
    Location
    Fort Worth, Tx
    MS-Off Ver
    2016
    Posts
    19

    Re: duplicate range n times and insert at blank row

    i haven't gotten to the point where the addition ranges include by inputting a month more than"1" have the correct date.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: duplicate range n times and insert at blank row

    So...The "Y" becomes part of a range to be duplicated n times on the Mid-Atlantic sheet
    What cell refers to the n times you mentioned earlier...
    i haven't gotten to the point where the addition ranges include by inputting a month more than"1" have the correct date.
    This means what?

  9. #9
    Registered User
    Join Date
    01-08-2008
    Location
    Fort Worth, Tx
    MS-Off Ver
    2016
    Posts
    19

    Re: duplicate range n times and insert at blank row

    i just didn't want to confuse you if you looked at the data sheet and saw that on'Mid-Atlantic all the dates were the same. Sorry, that may have been more confusing by bringing it up.

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: duplicate range n times and insert at blank row

    So step by step explanation of the process...

    User enters Y or N next to provider...The Y makes up the range...
    Where does he enter X amount for duplicate times...
    Then this gets duplicated on Mid-Atlantic sheet which first gets cleared I assume before re-populating

    One need s to understand your thinking...
    You...We are not...

    Perhaps you should mock up a sheet with a before and after setup...This will ensure that we don't go around in circles trying to understand your requirement...
    Last edited by sintek; 05-23-2020 at 09:59 AM.

  11. #11
    Registered User
    Join Date
    01-08-2008
    Location
    Fort Worth, Tx
    MS-Off Ver
    2016
    Posts
    19

    Re: duplicate range n times and insert at blank row

    Put 'y' or 'n' in column a of 'personnel' tab. enter quantity of months you want ot review in C1 on 'Data' tab. Enter # of months to review in C3 of 'Data' tab. This creates data base on "Trend #1' tab (this tab is hidden normally as it is not used and not needed to be viewed). All this creates results on 'Mid-Atlantic' tab but leaves blank lines for personnel that received a 'n'. Trying to remove the resulting blank lines on 'mid-atlantic'

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: duplicate range n times and insert at blank row

    See attached...I removed all formulas and non essential sheets
    Is this close...
    Please Login or Register  to view this content.
    Could also use this I suppose...

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 05-23-2020 at 11:36 AM.

  13. #13
    Registered User
    Join Date
    01-08-2008
    Location
    Fort Worth, Tx
    MS-Off Ver
    2016
    Posts
    19

    Re: duplicate range n times and insert at blank row

    Sintek, thank you. I appreciate all the help. This works for a single use, but i am struggling to make it work for continuous updates. The intention was to have a spreadsheet that could have new personnel added monthly and create a report that has the names for each region on it with no blank lines between the months but with blank lines between the regions. Additionally i am trying to make the report capable of having multiple outputs based on the desired number of months reporting (typically 1,2, or 3). Also want to be able to run report with and without indicated personnel (hence the 'y' or 'n'). This is because the staff is very fluid and people leave frequently. It is desirable to be able to see what effect 1 person has on the region (by including or excluding them). We want to run this monthly with new people added and ability to exclude others. Basically this becomes a template. Once i have the single region template process down, i will add in the other regions in basically the same way.
    I thought that i would create a page for each region that was created from the functions and VBA and then bring them together on 1 summary page as the template. this way i could sum the information on the columns to the right of the personnel info (not included as its input is based on the personnel info created). Was i way off base with my original use of functions and is this easier with 100% VBA?

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: duplicate range n times and insert at blank row

    This works for a single use
    Was this not your initial requirement?

    From the above post I have no clue what your requirement really is...

    Like in my previous posts...Upload a sample file and explain step by step what you are wanting to achieve...
    Manually mock up your requirement and expected result...Give a few examples...

+ 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. [SOLVED] Insert Symbol If a Range of Cells Aren't Blank?!
    By exceln3wb in forum Excel General
    Replies: 3
    Last Post: 09-26-2017, 01:14 PM
  2. [SOLVED] Insert Blank Row - If Range contains a value
    By hftechno in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-21-2014, 09:31 AM
  3. [SOLVED] Macro to insert a rage of cells deletes previous content if range is blank
    By 54ed in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-24-2014, 12:01 AM
  4. [SOLVED] Insert blank row after duplicate account number
    By kcasey1318 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2014, 04:50 PM
  5. Macros to insert 2 blank row and autosum a column after every 200 rows in data range.
    By sachinsingh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-22-2013, 09:55 AM
  6. Insert a blank row in middle of a range
    By batman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-14-2007, 05:49 PM
  7. variable - insert blank row/select range
    By Jan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-13-2006, 12:45 PM

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