+ Reply to Thread
Results 1 to 10 of 10

Macro to sort and insert rows HELP

  1. #1
    Registered User
    Join Date
    12-17-2012
    Location
    Wolverhampton,England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Macro to sort and insert rows HELP

    Using Windows 7 and excel 2007
    I wish to use the paste all function from excel clipboard to paste into a spread sheet but the data I copy can be between 2 to 7 rows .The macro would have to search for the words LIFETIME RECORD down column A and if the rows between each LIFETIME RECORD is less than 7 rows apart to insert rows or row above the next LIFETIME RECORD so rows 1,8,15,22,29 etc. would start with LIFETIME RECORD
    The amount of clips can vary from 2 to 24
    Many Thanks
    Steve
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to sort and insert rows HELP

    hi pennbowl, option to make it, please check attachment, press "Run" button or run code "test"
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-17-2012
    Location
    Wolverhampton,England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro to sort and insert rows HELP

    Thanks for quick response and it does what I asked for but I wish to paste it back in Paste Special Text
    I have included another attachment to try to explain what I am trying to do
    If you click on HS 1 copy data from web pages

    http://www.racingpost.com/horses/hor...abs=horse_form

    and use the red macro to paste in column L the macro is

    Please Login or Register  to view this content.
    This works OK but if possible I wish to use it with the Paste all function from the clipboard for
    I tried to copy the result from your macro but can't paste as Text and values for formula on sheet HS won't recognize imported data
    Thanks again
    Steve
    Attached Files Attached Files
    Last edited by jeffreybrown; 12-25-2012 at 12:16 PM.

  4. #4
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to sort and insert rows HELP

    Steve, I do not quite understand your request. If I'm right, copy data from a web page mentioned above and try to use this code instead:

    Please Login or Register  to view this content.
    PS. You need to use special code tags - # - sign on the message format toolbar - while posting any code on the forum.

  5. #5
    Registered User
    Join Date
    12-17-2012
    Location
    Wolverhampton,England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro to sort and insert rows HELP

    Just to say a BIG thank you watersev for your help .What I did is use your formula which I have adjusted as follows.

    Please Login or Register  to view this content.
    Then I use

    Please Login or Register  to view this content.
    AND THEN USE

    Please Login or Register  to view this content.
    This will clean the two sheets "H" and "B" so I can use those sheets again and again

    The only thing I would wish to achieve is instead of selecting sheet "H" to use the Paste all Clipboard button in Excel 2007
    I would like to perform that task by using a macro
    as said above I manually have too click on sheet "H" cell A1 and press the Paste all Clipboard button
    So if a macro is possible I could combine the macros together to save time and not have to switch between sheets.
    This is a very long CHRISTMAS LIST but also a macro for deleting the clipboard may come in handy.
    Just to repeat
    A VERY MERRY CHRISTMAS TO ALL ON THIS SITE
    ESPECIALLY YOU "WATERSEV"
    AND HAPPY NEW YEAR
    Many THANKS
    Steve.
    Last edited by jeffreybrown; 12-25-2012 at 12:17 PM.

  6. #6
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to sort and insert rows HELP

    I'm afraid I'm not with you on your requests. If you mean using code to copy data from web page and get it correctly on the sheet then please check attachment, instructions are on the first sheet
    Attached Files Attached Files

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,164

    Re: Macro to sort and insert rows HELP

    Hi Steve and a Merry Christmas to you also.

    Please notice code tags have been added for you, but please do this in the future as it makes the thread easier to follow...Thanks.
    HTH
    Regards, Jeff

  8. #8
    Registered User
    Join Date
    12-17-2012
    Location
    Wolverhampton,England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro to sort and insert rows HELP

    I would like to perform that task by using a macro
    as said above I manually have too click on sheet "H" cell A1 and press the Paste all Clipboard button
    So if a macro is possible I could combine the macros together to save time and not have to switch between sheets.
    This is a very long CHRISTMAS LIST but also a macro for deleting the clipboard may come in handy.

    Thanks again for your reply but using

    3. Press CTR L+v while Excel being active application
    control
    only pastes the last copied item

    My excel book has many sheets see attached file.
    If I was looking at sheet "CHASE"
    I would like a macro that would connect to excel and tell excel to paste all the data on the clipboard to sheet "H" cell A1 but doing that operation while the "CHASE" sheet is activated (I am viewing "CHASE" sheet)
    I don't know if this is possible??
    As said previously I have to change sheet to sheet "H" cell A1 then press paste ALL using clipboard function button.
    The macro would save me from changing sheets.
    Thanks again
    Steve
    Attached Files Attached Files
    Last edited by pennbowl; 12-26-2012 at 06:53 AM.

  9. #9
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to sort and insert rows HELP

    please check attachment, select cells to copy, press ALT+Right arrow
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-17-2012
    Location
    Wolverhampton,England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro to sort and insert rows HELP

    When I press ALT+Right arrow
    What happens is that the active cell moves to the farthest point to the right?

    I'm looking at V BA the macro below doesn't work but to connect to the clipboard I believe you have to use some type of V BA

    Sub Get Clipboard Text()
    Dim obj Data As New Data Object
    ob j Data.Get From Clipboard
    v Text = obj Data.Get Text
    Sheets("H") .Range ("A1") .Value = v Text
    End Sub

    Still scratching Head
    Thanks Guru

+ 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