Pasting Selections from Multiple Sheets along with their Sheet's Name

[6afraidbecause789]

Hi Programmers,
I'm trying to copy data from 7 sheets, put it together on a report
sheet, categorize it, and sort it. There are things the macro
recorder just can't do in the below routine. Any help on programming
this would be greatly appreciated. Thanks for your consideration and
time.

STEPS
--go to sheet 1 (named "Period 1")
--select B13:C13, Z13:AA13, AC13:AD13, AO13:AP13
--continue selecting the cells in the same columns for every odd row
below 13 that has text in column C (end of data on each sheet will
vary)
--copy this entire selection
--go to a sheet named "Grades Summary"
--paste values only, starting on row 3 (headings in row 2)
--in column 9 (since there are 8 cols involved above), put the name of
the previous sheet ("Period 1") on every row of the pasted data

--then, go to sheet 2 (named "Period 2")
--select the same as above
--return to the Summary sheet
--**go to a row below any values that are already pasted**
--paste values only
--in column 9, put the name of the previous sheet (this time "Period
2") on every row of the newly pasted data

--repeat this for all 7 sheets (Period 1, Period 2,...Period 7)

--select all in the Summary sheet and sort by columns A and B;
headings are in row 2.

--lastly, go to print preview

Thanks

 

[Bernie Deitrick]

Sub TryNow()
Dim i As Integer
Dim j As Integer
Dim myR As Long
Dim strR As String
strR = "B13:C13, Z13:AA13, AC13:AD13, AO13:AP13"

For i = 1 To 7
myR = Sheets("Period " & i).Cells(Rows.Count, 3).End(xlUp).Row
For j = 13 To myR Step 2
Sheets("Period " & i).Range(strR).Offset(j - 13).Copy _
Sheets("Grades Summary").Cells(Rows.Count, 1).End(xlUp)(2)
Sheets("Grades Summary").Cells(Rows.Count, 1).End(xlUp)(1, 9).Value = "Period " & i
Next j
Next i

End Sub

HTH,
Bernie
MS Excel MVP

 

[6afraidbecause789]

Very slick Bernie. Thank you.