+ Reply to Thread
Results 1 to 6 of 6

Looping Macro to cut rows based on value and paste them into new sheets

  1. #1
    Registered User
    Join Date
    09-06-2013
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2003
    Posts
    45

    Looping Macro to cut rows based on value and paste them into new sheets

    Hi all,

    I am working on a project that requires me to sort some data ready to be loaded into a database.

    The initial data comes with a series of field headers for each 'DATA_TYPE' and the corresponding records for that data type below. There is then a blank row, and then the layout is repeated for the next relevant data type...and so on. (please see sample sheet)

    I am looking to create a macro that will start from cell A:A and work down the sheet cutting all data of a particular type to then paste it into a newly created sheet. Ideally this sheet will be named based on the corresponding 'DATA_TYPE#' value (again please see sample sheet)
    Once that particular Data type has been pasted into the new sheet, the process should repeat for the next type...and so on.

    This process should repeat until each different data type (along with 'TITLE' row) has been pasted into its own sheet.

    *As the data i receive will be split at random row intervals each time(eg, if DATA_TYPE1 happens to have 5 entries instead of 3 then DATA_TYPE2 will be shifted down) the macro will need to be based upon where blank rows are, not necessarily where new data types start in the sample document*

    I would appreciate any and all guidance that you are able to offer.

    Thank you.

    Sort_data_test1.xlsx
    Last edited by Ethanrholt; 09-17-2014 at 06:00 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Looping Macro to cut rows based on value and paste them into new sheets

    If the data is coming in as you have shown, then you'll need to loop column A looking for blanks, so you'll copy from the last blank to the current one.

    So you can do something like this, to find them, it doesnt copy, but you should be able to sort tha

    Please Login or Register  to view this content.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Looping Macro to cut rows based on value and paste them into new sheets

    FWIW:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    09-06-2013
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Looping Macro to cut rows based on value and paste them into new sheets

    Thank you both very much for your responses.

    Nathansav, your suggestion didn't quite seem to complete the process as I would require it but thank you for your comment.

    John H. Davis, that seemed to work perfectly. Thank you so much. I really appreciate your time. I will now attempt to reverse engineer your code in a vain attempt to be able to understand and replicate it in the future.

    Thanks again all!

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Looping Macro to cut rows based on value and paste them into new sheets

    You're welcome. Glad to help out and thanks for the feedback.

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Looping Macro to cut rows based on value and paste them into new sheets

    No worries, i did mention you'll need to add the copy bit in and adding sheet in. Thought you'd like a challenge

    You only need to add the copy after the select, then worksheets.add called strName then paste. 3 lines of code or so

+ 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] Copy from different sheets- data based on selection and paste rows in new sheet
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2014, 03:30 AM
  2. Replies: 1
    Last Post: 10-04-2013, 04:21 AM
  3. Compare two sheets,copy and paste unique rows based on values in 2 columns
    By ooggiemobile in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-02-2013, 03:58 AM
  4. Identify specific rows based on a value across multiple sheets, and paste into new
    By optimisticsam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2011, 10:02 AM
  5. Looping Through Some Sheets to Sort Copy & Paste
    By luke1438 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2010, 12:57 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