+ Reply to Thread
Results 1 to 7 of 7

Paste rows with gaps between them in certain order (VBA)

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    2007
    Posts
    35

    Paste rows with gaps between them in certain order (VBA)

    Hi everyone,

    I have a very particular but simple format that I need to keep for a sheet and unfortunately I need to be able to let any user of it break free of any automation and just enter values (so I made a paste pivot table values macro but we have some real technophobes here). I've provided a pivot example and my desired text-format result so it is clear how everything should be laid out.

    Attached is a very simple example of what I would like to be able to do:

    1. Take data that has been put into InfoEntry sheet (6 lines)
    2. Always paste the Profit/Loss per the accounts line (first) with the number to the right hand side of it.
    3. If there are entries that are tagged "Add" (second) or "Less"(third) then paste them next in the format given. (Simple compact layout with an indent)
    4. Paste the last line "Adjusted Profit"(fourth) and the amount given at the bottom but with double lines (as shown).

    I'd be very grateful to anyone who can help me with this. Thank you all for your time.

    Limebaish
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Paste rows with gaps between them in certain order (VBA)

    Hello limebaish
    Try the attached

    PasteRows 300113.xlsm
    If this was helpful then please click the small star icon at the bottom left of my post. A little appreciation goes a long way.

  3. #3
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    2007
    Posts
    35

    Re: Paste rows with gaps between them in certain order (VBA)

    Hi Barry,

    Thank you so much for your help with this, you're very kind Unfortunately in testing this I've come across a few problems but they might be purely because I am misunderstanding the code. Would you mind moving the button to the right hand side and adding some extra values (random names and amounts) and telling me how I need to change the code for future use? The main problem to come out of this however is that I would like for there not to be a gap if all were made to be "less". Currently it leaves a space and I'd like for that to disappear if possible. If there were no "less" or "add" options then I'd like that line for the adjusted profit to appear with a one row gap as usual.

    Thanks so much for your help, if I can ever repay the favour please do let me know.

    Limebaish

  4. #4
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Paste rows with gaps between them in certain order (VBA)

    Hello limebaish
    I think this now sorts out your requirements
    Revised version attached for your testing
    PS Be aware that yo have some hidden rows on your 'Pivot' sheet.
    This may obscure some of your results now that there is an extended list of expense categories - just unhide the rows as needs be.

    PasteRows 310113.xlsm

  5. #5
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    2007
    Posts
    35

    Re: Paste rows with gaps between them in certain order (VBA)

    Oooh so close! Unfortunately it doesn't cope with there being a gap in the range (for instance if a cell is blank because the cell it refers to is blank and so on). I think the whole hidden page pivot table set up might just be dragging my sheet down (drastically!) as well so I've sent you a part of it to have a look at so you can see how these numbers feed in. I've also added a sincere apology for taking up so much of your time. It's quite frustrating to be this close to having a solution, I hope you don't mind getting me through this last bit

  6. #6
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Paste rows with gaps between them in certain order (VBA)

    Hello limebaish
    Work so far has been to reconcile the user entry "table" on sheet 'InfoEntry' to produce the "Desired VBA Output" "table" on sheet 'Pivot' and I think this works.
    You also have other cell links going on in sheet 'Pivot' relating to the static table (6 lines, post #1?) in your example worksheet.
    As we have added other categories to the 'InfoEntry' "table" (not attempted to combine them), this has altered the layout of "Desired VBA Output" "table". This is why you are having trouble with your cell links.

    Whilst this can be fixed, on the basis of "incremental improvement", if you agree with my analysis perhaps you could mark this thread as SOLVED and pursue the next development through another thread?

  7. #7
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    2007
    Posts
    35

    Re: Paste rows with gaps between them in certain order (VBA)

    Absolutely Barry, thanks so much for the help

+ 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