Copy all sheets (sheet1, sheet2 etc to 9) and paste to sheet "Master"

[andymalan]

Hello all, i want to find out if there is a better way to copy from sheets1 through 9 and paste to sheet "master" one sheet below the other. here is the code i have cobbled together which does work but maybe there is a more efficient way?

' select the range to copy and paste to "master"
    Sheets("Sheet9").Select
    Rows("3:68").Select
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet8").Select
    Rows("3:68").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet7").Select
    Rows("3:68").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet6").Select
    Rows("3:68").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet5").Select
    Rows("3:68").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet4").Select
    Rows("3:68").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet3").Select
    Rows("3:68").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll Down:=-9
    Sheets("Sheet2").Select
    Rows("3:68").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet1").Select
    Rows("15:70").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Columns("A:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add2 Key:=Range( _
        "A2:A3734"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("A1:L3734")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Your help will be appreciated, and I will have learned something?
best regards, Andy

 

[Yongle]

Code tags make code easier to read (keeps indents etc) - Click on # icon above post window, code tags are auto-placed in the post window, and then ..[CODE ] paste code here [/CODE ]

Will you always copy EXACTLY the same ranges
- Sheet2 to Sheet9 Rows("3:68")
- Sheet1 Rows("15:70")

 

[footoo]

Try this (untested) :

Dim r%, n%
With Sheets("Master")
    .Rows("1:594").Insert
    Sheets("Sheet1").Rows("15:70").Copy .Rows(1)
    r = 71
    For n = 1 To 9
        Sheets("Sheet" & n).Rows("3:68").Copy .Rows(r)
        r = r + 66
    Next
    .[A:F] = .[A:F].Value
    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=Range("A2:A3734"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A1:L3734")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

 

[andymalan]

hi Yongle, yes and yes.

 

[Yongle]

My approach would be very similar to @footoo
- insert all rows first, then copy and paste, sort

As stated by @footoo, the code was posted without testing which provides you a method to use

A few lines I suggest you to look (with my suggestion and logic below in each case)

    .Rows("1:594").Insert ??
    .Rows("1:584").Insert    (56 X 1  + 66 X 8)

    r = 71 ??
    r = 56 (number of rows copied from sheet1)

    For n = 1 To 9 ??
    For n = 2 To 9 (sheet1 already dealt with)

I did not test other code, but this is how I would write the bit that sorts

 With Sheets("Master")
..... rest of code
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=Range("[COLOR=#ff0000]A2[/COLOR]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("[COLOR=#ff0000]A:F[/COLOR]")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

 

[footoo]

Yongle - thanks for correcting.

 

[footoo]

r = 56 (number of rows copied from sheet1)

Should be :

r = 57    (number of rows copied from sheet1, plus 1)

 

[Yongle]

@footoo - silly me!

 

[andymalan]

Thank you Footoo, your code does work, but it is taking longer to execute than the code I already have.
I do appreciate you wanting to help.

 

[andymalan]

Thank you for your help yongle, much appreciated.