Cut-Paste specified number of rows from different sheets into one sheet

  • I have a requirement where I am given ten sheets of data and a specified number of rows from each sheet need to be moved into Sheet2. I've mentioned this order in Sheet1 (sample attached).


    1. I need them to be cut-pasted because this process may repeat after one step of cut paste so I don't want duplication of data

    2. The macro should run only once (cut-paste once) because I should be able to change the values in Sheet1 if required before I run the macro again.

    2. Data needs to be pasted in the same order as specified in Sheet1

    3. All data columns need to be copied from the sheets (columns may vary across sheets, hence all available columns need to be cut-pasted)


    Any help would be very appreciated because I don't want to do this manually :)

    Files

    • Sample.xlsx

      (69.45 kB, downloaded 46 times, last: )
  • If you want to run the macro again after the first time, do you want the new data to be appended below the existing data in Sheet2 or do you want to delete the old data before pasting the new data?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • If you want to run the macro again after the first time, do you want the new data to be appended below the existing data in Sheet2 or do you want to delete the old data before pasting the new data?

    I need the data to be appended below, so at the ultimate end of the exercise, there will be zero data in all sheets except Sheet2 where all this data will exist.

  • Try:

    Code
    1. Sub CutRows()
    2. Application.ScreenUpdating = False
    3. Dim ws As Worksheet, desWS As Worksheet, sh As Range
    4. Set desWS = Sheets("Sheet2")
    5. For Each sh In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
    6. Sheets(sh.Value).Rows("2:" & sh.Offset(, 1).Value + 1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
    7. Sheets(sh.Value).Rows("2:" & sh.Offset(, 1).Value + 1).Delete
    8. Next sh
    9. Application.ScreenUpdating = True
    10. End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Works absolutely perfect! Thank you so much.

    One small thing: can we modify the code in a way that the sheet names don't matter? Because my sheet names aren't always Sheet2, Sheet3, etc. but the order of the sheets remain the same.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Please ignore the previous post and try this version:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Please ignore the previous post and try this version:

    Sorry I couldn't revert earlier. Just got to using the macro today, thank you. If I may ask, I have two more tweaks that can make my work much easier:


    1. Can we have a code to populate all the sheet names in the first sheet? Right now I'm getting a little confused as to which sheet has which data to specify the number of rows to cut-paste into Sheet2


    2. After each cut-paste, I need the data to be sorted based on the last column.

  • Sorry I couldn't revert earlier. Just got to using the macro today, thank you. If I may ask, I have two more tweaks that can make my work much easier:


    1. Can we have a code to populate all the sheet names in the first sheet? Right now I'm getting a little confused as to which sheet has which data to specify the number of rows to cut-paste into Sheet2


    2. After each cut-paste, I need the data to be sorted based on the last column.

    Forgot to add to point 2:

    2. After each cut-paste, I need the moved data set to be sorted based on the last column (not with the previous existing data in sheet2, but just that particular copy). Because each copy action is a different set and only that has to be sorted. Sorting should be in ascending order.

  • Quote

    Can we have a code to populate all the sheet names in the first sheet?

    I'm not really sure what you mean. The first sheet already has the sheet names in column A.


    Also, it's hard to see what kind of data needs to be sorted because all the cells in the last column in each sheet in your sample file contain the same data. I think that it would be easier to understand what you want to do if you could attach your actual file with the data de-sensitized if necessary.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I'm not really sure what you mean. The first sheet already has the sheet names in column A.


    Also, it's hard to see what kind of data needs to be sorted because all the cells in the last column in each sheet in your sample file contain the same data. I think that it would be easier to understand what you want to do if you could attach your actual file with the data de-sensitized if necessary.

    My bad, I had a feeling my post wasn't clear. This is what my work flow looks like:


    1. I have a single spreadsheet with data that I need to first split into different sheets based on values in a particular column.

    2. I already use a macro that does this splitting for me and names the sheets with the respective column values (eg. male, female, etc.)

    3. I then use your macro to specify how much to copy from each sheet and create a dataset in sheet2 (eg. copy 40 rows from male and 30 from female).

    4. Right now I am manually entering each sheet name into column A in the first sheet, I was wondering if there was a way to auto-populate these sheet names in column A so I know how much I need to copy from which sheet. This is because the data is dynamic and isn't always the same sheet names.


    Regarding the sorting part, I figured a way to do it without the macro, so we could leave that out.

  • Glad it worked out. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Mumps, I was running the file super good until today when I ran into "Excel cannot complete this task with available resources". When I run the macro, my task manager shows Excel using close to a GB of memory. I believe this happens when there are too many data ranges? After trying everything I could find on the net, I came across this solution that supposedly could fix it -

    . But I'm stuck with the implementation. I have attached my file too. Could you kindly have a look?

  • For some reason all of the sheets had a used range up to the last column which is 16384 even though they were all blank except for the first two. In the attached file I have deleted all the extra blank columns. Also, in the Splitdatabycol macro, replace this line of code:

    Code
    1. icol = ws.Cells(1, Columns.Count)

    with this line:

    Code
    1. icol = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1


    See if this makes a difference.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Oops! Replace those two lines of code with these:

    Code
    1. icol = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
    2. ws.Cells(1, icol) = "Unique"

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Mumps, I just managed to find the code for my requirement via Google. Thank you so much for your help.

    Since I didn't write this code, rather than trying to decipher it, it might be easier for me to help if you could attach copy of your file that includes the single spreadsheet with data and the macro that you currently use that does this splitting for you and names the sheets with the respective column values. Then explain in detail, step by step referring to specific cells, rows, columns and sheets exactly what you want to do.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.