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

Quote from Mumps

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.

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.

Quote from Mumps

Please ignore the previous post and try this version:

Code

Sub CutRows()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, sh As Range
    Set srcWS = Sheets(1)
    Set desWS = Sheets(2)
    For Each sh In srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp))
        With Sheets(sh.Value).Rows("2:" & sh.Offset(, 1).Value + 1)
            .Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            .Delete
        End With
    Next sh
    Application.ScreenUpdating = True
End Sub

Display More

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.

Quote from Hyperventilate

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 from Mumps

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.

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

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 -

I couldn't find that particular line of code, what I see is:

icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"

Quote from Mumps

Oops! Replace those two lines of code with these:

Code

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

I did this but now the macro doesn't do anything when I run it. Just starts and stops.

Mumps, I just did a new file with your code and now it works well. Also very fast without hogging RAM. Thank you again!