+ Reply to Thread
Results 1 to 33 of 33

Copy and insert set of rows Macro

  1. #1
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Copy and insert set of rows Macro

    Hi Excel Forum,

    I am currently using a macro that copies a set of 5 rows and pastes them after the last row with data in it on the same page.

    The code i am currently using is -

    Please Login or Register  to view this content.
    I was wondering if it was possible to adapt this code that that it changes the row refrence in column b up 5 everytime the macro is used for example. Cell formula reference changes from B:10 to B:15 then B:15 to B:20, i hope that makes sense.

    I also was wondering if it was possible when it adds the new rows it also asks what worksheet you would like to connect the formulas to. As the new row will link to the same Columns and Rows but just on a different Tab.

    I hope this makes sense i find it a bit hard to explain myself.

    Thanks
    I Hope someone can help

    James

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    For shifting down 5 rows you need to do this-
    Please Login or Register  to view this content.
    Not able to understand your second requirement If you could elaborate with an example or even attach a sample sheet.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  3. #3
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Quote Originally Posted by sourabhg98 View Post
    For shifting down 5 rows you need to do this-
    Please Login or Register  to view this content.
    Not able to understand your second requirement If you could elaborate with an example or even attach a sample sheet.
    Hi Thank you for your response, what does this offset do?

    I want it to paste the copied cells after the last row with data. and then change the Row Value in the formula up by 5 everytime. As i want the copied cells to pick up data further down the sheet on another tab.

    Is this a bit clearer sorry!

  4. #4
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    The code i have already copies and places the cells to the correct place i am wondering if the code can be adapted to change row values in formulas.

  5. #5
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    I would recommend you to use this code for determining the last row-

    Please Login or Register  to view this content.
    This is what it does-
    It goes to column B, the last row of your sheet. Then it uses the end function to move up to the end, until it recognizes some data.
    This is the same like we do manually- Going to the Last cell of column B. Then pressing CTRL + up arrow key.
    This way we can determine the last row, instead of every time adding on 5 rows to the variable.

  6. #6
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Im not sure if this was 100% what i was looking for!

    Please see attached sample book.

    Please look at the Exam Data input tab when the insert New Student rows is clicked the new rows appear underneath the last set on the sheet.

    I was wondering if you could make it so everytime a new set of rows was added it moves the formula up for example from ='Student Data & Details'!B15 to ='Student Data & Details'!B20 then when the next is added the new ones change from ='Student Data & Details'!B20 to ='Student Data & Details'!B25.

    i hope this has made it more clear!

    Thank you for your help so far!

    i can be really bad at explaining things sometimes!
    Last edited by Jamidd1; 02-20-2016 at 06:03 PM.

  7. #7
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    maybe you need this-
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Thank you that seems more like it! Although when i just tried to use it it came up with some sort of error but just said 400 in a command box

    Your being a major help!

  9. #9
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    It is not giving any error for me-
    Check attached.
    I hope you were talking about the button on the Sheet - "Exam Data Input".
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Thats so stange!

    On mine it is now copying the rows but is not adding 5 mine has jumped to B32 for some reason!

  11. #11
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    Can you attach the file where it jumped to B32, as you can see in the file attached by me it shows B20.

  12. #12
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Quote Originally Posted by sourabhg98 View Post
    Can you attach the file where it jumped to B32, as you can see in the file attached by me it shows B20.
    There you go my friend!
    Last edited by Jamidd1; 02-20-2016 at 06:03 PM.

  13. #13
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    That screwed me up
    This had some sort of problem to do with Merged Cells, you might have changed something in the first merge for John Smith.
    I un-merged and merged it back. Now this seems to work fine.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    I guess i can just use the one that you sent me that works! Do not have a clue why my one isnt working!
    i have another question along the same line but in terms with the same button but on the Student Data & Detail tab, i zm wondering if i can make it change the worksheet in the formula to the next student tab that has been created?

    Do i have to create a new post to ask this?

  15. #15
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Quote Originally Posted by sourabhg98 View Post
    That screwed me up
    This had some sort of problem to do with Merged Cells, you might have changed something in the first merge for John Smith.
    I un-merged and merged it back. Now this seems to work fine.
    Ahh thank you! your a star ill check it out now!
    Strange wasnt it ! im sure i did not do anything to the cell haha!

  16. #16
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Hi Soura,

    I am getting the jumping to cell 32 problem again, i think i know what is causing the problem, so i can filter on the Exam Data tab page i have to enter the formula into all 11 cells in A that represent that student and then format paint over the top just the one name if that makes sense.

  17. #17
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    Okay, here you go-
    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Thank you so much! How did you do that?

    I have noticed though say if i delete the Toby ***** and then use the insert button the cell do not go directly under the John Smith cells do you know why this is?

  19. #19
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Thank you so much! How did you do that?

    I have noticed though say if i delete the Toby ***** and then use the insert button the rows do not go directly under the John Smith rows do you know why this is?

  20. #20
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    That is happening because of the hidden "John Smith" in column AA.
    I think you forgot to remove that

  21. #21
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Haha my bad! me being a complete mug haha!

    Thank you for all your help! Made the task of adding new students 100 Times easier

  22. #22
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    Happy to Help

    If you have no further queries please mark this thread as "Solved" from the "Thread Tools" at the top of this page.

    Have a Great Day!!

  23. #23
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    One more thing how did you make the John Smith go like that so its not merged? i added a few more bits onto my one so dont want to have to do it all again!

  24. #24
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    All the 11 cells contain the same formula, as you said it has to be filtered. So the font color of the 10 cells except the middle one has been changed to the background "light grey" color, so only middle cell is visible. So, macro copies all the 11 rows, then formulas for all the 11 cells are changed accordingly.

  25. #25
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Thanks!

    I am having no luck trying to place this code into the workbook that has been updated a bit everything is the same on that sheet so i dont understand why it is not working

  26. #26
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Thanks!

    I am having no luck trying to place this code into the workbook that has been updated a bit everything is the same on that sheet so i dont understand why it is not working
    Last edited by Jamidd1; 02-20-2016 at 06:05 PM.

  27. #27
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Sorry fixed it to eager to ask for help!! i need to slow down haha!

  28. #28
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Quote Originally Posted by sourabhg98 View Post
    Happy to Help

    If you have no further queries please mark this thread as "Solved" from the "Thread Tools" at the top of this page.

    Have a Great Day!!
    Dear Sourabhg98, I am now having an issue that everytime the macro is run my Exce seems to crash and restart!!! you wouldnt know why this was would you?

  29. #29
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    There is no reason with me to say it would crash anyhow?
    Can't tell until I see the worksheet and the error.

  30. #30
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Its when say i insert the rows on the Student Details tab change the name to what i want then go to the exam input tab and click the insert rows it just crashes very strange.

    It as if if i do anything at all before clicking the insert rows button it crashes.

    I have managed to add a certain doing it aa certain way but its not very user friendly!
    Last edited by Jamidd1; 02-20-2016 at 06:05 PM.

  31. #31
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Copy and insert set of rows Macro

    Works perfectly fine for me!

    This is the result I got.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Screen Shot 2016-02-19 at 11.00.35.png

    This is the message i get everytime!

    So i go to the Student Details tab insert the rows by cliking the button, I then change the name from John Smith to any random name, when i then go to the Exam Data input tab and try to insert the rows it just crashes and gives me the message above.

  33. #33
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Copy and insert set of rows Macro

    Quote Originally Posted by sourabhg98 View Post
    Okay, here you go-
    Please Login or Register  to view this content.
    Hi Soura,

    I was wondering how I would adapt this formula to add 7 instead of 5 to the formula, I have had to add a few more rows. Thanks

+ 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] Macro - copy and insert multiple rows
    By yedry101 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-09-2013, 06:25 PM
  2. Replies: 0
    Last Post: 11-12-2013, 04:56 PM
  3. Replies: 0
    Last Post: 05-10-2013, 12:04 PM
  4. [SOLVED] Macro - copy and insert multiple rows
    By Shazzapg in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-22-2013, 10:37 AM
  5. Copy and insert rows using macro
    By adonis_082002 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2013, 06:33 AM
  6. Replies: 2
    Last Post: 04-21-2011, 07:13 PM
  7. Macro to insert & copy rows
    By john1985 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2011, 11:52 PM
  8. Insert rows and copy macro
    By jcdifi26 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-14-2009, 06:16 PM

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