I am putting together a workbook that consolidates quarterly sales
from many departments by day of week so they may bee seen and averaged
by day of week on one worksheet. The following macro put together for
Saturday does it very well for five departments but its way too
lengthy. Is there a way of paring it down to something more
manageable? I have many more departments and days to insert and
writing this is tedious. The macro I’m using is below. Also, where do
I place Application.CutCopyMode = False to clear the clipboard? The
weeks vary from 9-12 depending on the calendar.

Thanks for your help in advance; I’ve learned a lot from you guys.

Sub CombineSat()
On Error GoTo EndOfMacro
'Week Number
'1
Windows("combined sheets.xls").Activate
Sheets("Sat").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AD4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'2
Windows("combined sheets.xls").Activate
Sheets("Sat (2)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AE4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'3
Windows("combined sheets.xls").Activate
Sheets("Sat (3)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AF4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'4
Windows("combined sheets.xls").Activate
Sheets("Sat (4)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AG4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'5
Windows("combined sheets.xls").Activate
Sheets("Sat (5)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AH4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'6
Windows("combined sheets.xls").Activate
Sheets("Sat (6)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AI4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'7
Windows("combined sheets.xls").Activate
Sheets("Sat (7)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AJ4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'8
Windows("combined sheets.xls").Activate
Sheets("Sat (8)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AK4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'9
Windows("combined sheets.xls").Activate
Sheets("Sat (9)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AL4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'10
Windows("combined sheets.xls").Activate
Sheets("Sat (10)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AM4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'11
Windows("combined sheets.xls").Activate
Sheets("Sat (11)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AN4").Select
Selection.PasteSpecial Paste:=xlPasteValues
'12
Windows("combined sheets.xls").Activate
Sheets("Sat (12)").Select
Range("F6:F11").Select
Selection.Copy
Windows("Consolidated Worksheet.xls").Activate
Range("AO4").Select
Selection.PasteSpecial Paste:=xlPasteValues

EndOfMacro:
Exit Sub


End Sub