+ Reply to Thread
Results 1 to 19 of 19

Insert Copied Cells multiple times, shift cell down

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Insert Copied Cells multiple times, shift cell down

    hi
    i have data with up to thousands of rows down, there's a blank row in between them, and now i like to enter sub info using =formula in that blank rows, its going to be the same sub info for all line, but this sub info can go up to 1000 rows

    at the moment i can copy this sub info from the 1st line and insert copied cell in the 2 blank row with shift cell down, no problem, works great but not so great if you have thousand of line to be inserted with this sub info, i'm talking about almost ten thousands, probably going to take over 3-6 months to do this,
    can anyone please help me here

    option that i thought might help: create enough space(if 900 rows needed than insert new 899 rows in between? 899 blank rows x lets say(1000 main info) so i can paste multiple times? that still going to take months

    please help

    everything from row 2 to row 29 is a sub info that need to be copy underneath main info

    thank you thank you thank you
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    423

    Re: Insert Copied Cells multiple times, shift cell down

    Clean out your test sheet leaving only the first range of SubInfo and have a try with this macro, no idea on how much time it will take as I tested on a cut down sheet with only a 100 SubMain:
    Please Login or Register  to view this content.
    Last edited by rollis13; 09-10-2012 at 05:25 PM.

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    thank you very much, i have plenty of document need to done like the one i attached, i start to see some light i can finish it now,
    bad news is i am not fimiliar with macro, never use that feature as a matter of fact, is it possible you give me a step by step instruction to apply your suggestion??

    again thank you very much

  4. #4
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    423

    Re: Insert Copied Cells multiple times, shift cell down

    Click on the sheets' name tab | click View Code | paste the macro in the right panel | go back to your sheet and press Alt+F8 and use the macro.
    Or press Alt+F11 to view VB | then on the left select your sheet | paste the macro in the right panel | go back to your sheet and press Alt+F8 and use the macro.
    Please test on a copy of your Excel file.
    Last edited by rollis13; 09-10-2012 at 04:06 PM.

  5. #5
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    in a =a1, =b1, =c1 format or 1 2 3?i should leave only 1st sub main info

    since you tested with 100 sub main, what happen if i want to copy it 1000, 1001, 6327(any number basically) do i need different macros?

    thank you

  6. #6
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    423

    Re: Insert Copied Cells multiple times, shift cell down

    To use the macro just cut the rows from 31 to 58 of the sheet you attached to post #1 since as submain you have only rows from 2 to 29. If I correctly understood your requirement these are the only rows to be repeated through the sheet.

    No need of changes, the macro will (should) run till it fills up (and then crash) all the available rows (last row for my Excel 2003 is 65536) whatever the number of rows for Subinfo or Submain.
    Attached Files Attached Files
    Last edited by rollis13; 09-10-2012 at 06:12 PM.

  7. #7
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    hi rollis13

    what if i paste the sub info first the the main, sound simpler, what do you think?

    lets say i have 3000 main info means 3000 sub info, i copy the main info first 3000x times, then ( or first) put a row in between them, when its done the i paste the main info, of course the sub info will be all 0 0 0 until i enter the main info

    or should i do what you suggest me bit by bit so the system wont crash, says 100 main info each time?

    thank you

  8. #8
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    hi rollis13

    what if i paste the sub info first the the main, sound simpler, what do you think?

    lets say i have 3000 main info means 3000 sub info, i copy the main info first 3000x times, then ( or first) put a row in between them, when its done the i paste the main info, of course the sub info will be all 0 0 0 until i enter the main info

    or should i do what you suggest me bit by bit so the system wont crash, says 100 main info each time?

    thank you

  9. #9
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    hi rollis

    can i use the macro above if my main info is more than 8 numbers (column h)?? it can go up to 24 numbers, do i only to edit it a bit? where?

    thank you

  10. #10
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    423

    Re: Insert Copied Cells multiple times, shift cell down

    Yes, the SubMains may have more than 8 columns but the 1st and 2nd have to be at least 1 column more than all the rows of the SubInfo.
    Here is where the test is done to calculate the rows of the SubInfo. Column "H" may be changed but must be at least 1 more than the SubInfo:
    Please Login or Register  to view this content.
    No need to put a blank row in between the SubMains, just change 1 line of the macro:
    Please Login or Register  to view this content.
    To work the macro needs to find in your sheet at least 2 SubMains and the first set of SubInfo after the first SubMain.
    You can easily calculate your total rows to avoid the crash. You know how many rows your sheet manages so ... you know how SubMains you have, you know how many rows a SubInfo has ... just multiply.
    Last edited by rollis13; 09-12-2012 at 06:39 PM.

  11. #11
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    hi rollis13

    i understand that main info just need to be more than subinfo (in this matter more than 6 numbers) , thanks for that, but what if the subinfo is more that 6 numbers? in example below (9 numbers) what changes need to be make?

    example

    main: 1 2 3 4 5 6 7 8 9 10 11 12
    subinfo 1 2 3 4 5 6 7 8 9
    1 2 3 4 5 6 7 8 10
    1 2 3 4 5 6 7 8 11
    1 2 3 4 5 6 7 8 12

    main info more than 8
    subinfo more than 6

    what changes need to be make?

    kind regards

  12. #12
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    423

    Re: Insert Copied Cells multiple times, shift cell down

    As for the main just test the last column (L=12) to calculate the number of rows used by the subinfo so change the code:
    Please Login or Register  to view this content.
    If the subinfo has more than 6 columns just change the "F" (F=6) to the letter of the last column (I=9) of the subinfo range in the code:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by rollis13; 09-13-2012 at 12:56 PM.

  13. #13
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    hi rollis13

    thank you very much, i really appreciate it, you really have help someone

    kind regards

  14. #14
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    423

    Re: Insert Copied Cells multiple times, shift cell down

    Glad being of some help .

  15. #15
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    hi rollis13

    i have 1 more question please, example attach

    after all subinfo are being paste, i like to combine or merge those subinfo cells in to 1 cell using =A2&" "&B2&" "&C2&" "&D2

    and copy it down but only the subinfo cell, double click on the + does help, but the process stop because of the empty row
    right, i actually don't need an empty row, and yes you've told me how

    the question is

    if there's no empty rown in between, can i paste those merging formula but skip every 16th row(main info)

    but i like to do this after i apply the macro

    i know if only i don't need to skip the main info and no empty row blocking, i can paste the whole thing in a single click

    or maybe after, i can copy the result and paste it to a new document and the delete every 16th row there??

    kind regards
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down(solved)

    never mind about the last question, i include the merging formula in the macro and work perfect

    thank you very much

  17. #17
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Insert Copied Cells multiple times, shift cell down

    @ system

    Welcome to the forum.

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  18. #18
    Registered User
    Join Date
    09-12-2012
    Location
    DUBAI
    MS-Off Ver
    2007
    Posts
    65

    Re: Insert Copied Cells multiple times, shift cell down

    Hi System,

    A simple way of doing this thing is,

    first copy and paste complete data as it is in the second sheet,now in second sheet give the formula =if(sheet1!a2>=1,sheet1!a2,sheet1!a1). in one blank row type sub info. now copy the formula then paste using this combination shift+cntl+end, the select one column and then paste. then copy entire sheet and paste as value. work is done.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    09-07-2012
    Location
    perth, australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Insert Copied Cells multiple times, shift cell down

    hi solomon14all

    can you please rephrase your instruction

    thank you

+ 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