Below is part of a Macro I recorded. There are 17 sheets out of 51 that I want to sort specific columns (AA1:DF225) then copy specific cells (CK2:CK15) in one column and then paste, value, transpose to the sheet named "Summary". It needs to paste into the "Summary" sheet into the next lower row starting at cell E28. Everyone of the 17 sheets are named differently and are setup the exact same way, so the sorting is over the same cell range and the copying is the same 15 cells in the same column. I am a real "Green Horn" when it come to VBA. The recorded macro is pages long, I just think there has to be a better, shorter way to do this.
Code:
Sheets("Pointe").Select
Range("AA1:DF225").Select
Selection.Sort Key1:=Range("CK2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CK2:CK15").Select
Selection.Copy
Sheets("Summary").Select
Range("E28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Heights").Select
Range("AA1:DF225").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("CK2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CK2:CK15").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Summary").Select
Range("E29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Springs").Select
Range("AA1:DF225").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("CK2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CK2:CK15").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Summary").Select
Range("E30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Any help or suggestions would be greatly appreciated.
Thanks
Luke
Bookmarks