There's got to be a better way

[Preschool Mike]

I'm trying to collect the contents from a range of cell on several worksheets
(e.g., goal1, goal2, goal3, etc) and place them on my sheet named Yearly%.
While the code below works I was hoping there was a shorter or better way to
write it. Perhaps an array of some sort.

Dim August As Variant
Dim September As Variant
Dim October As Variant

Sub CollectandEnterFirstQuarter()
If Range("'Goal1'!E5") = "August" Then
August = Range("'Goal1'!BC9:BC17")
Range("'Yearly%'!B7:B15") = August
August = Range("'Goal1'!BC27:BC35")
Range("'Yearly%'!B16:B24") = August
August = Range("'Goal2'!BC9:BC17")
Range("'Yearly%'!P715") = August
August = Range("'Goal2'!BC27:BC35")
Range("'Yearly%'!P1624") = August
August = Range("'Goal3'!BC9:BC17")
Range("'Yearly%'!AD7:AD15") = August
August = Range("'Goal3'!BC27:BC35")
Range("'Yearly%'!AD16:AD24") = August
August = Range("'Goal4'!BC9:BC17")
Range("'Yearly%'!AR30:AR38") = August
August = Range("'Goal4'!BC27:BC35")
Range("'Yearly%'!AR39:AR47") = August
August = Range("'Goal5'!BC9:BC17")
Range("'Yearly%'!B30:B38") = August
August = Range("'Goal5'!BC27:BC35")
Range("'Yearly%'!B39:B47") = August
August = Range("'Goal6'!BC9:BC17")
Range("'Yearly%'!P3038") = August
August = Range("'Goal6'!BC27:BC35")
Range("'Yearly%'!P3947") = August
August = Range("'Goal7'!BC9:BC17")
Range("'Yearly%'!AD30:AD38") = August
August = Range("'Goal7'!BC27:BC35")
Range("'Yearly%'!AD39:AD47") = August
August = Range("'Goal8'!BC9:BC17")
Range("'Yearly%'!AR30:AR38") = August
August = Range("'Goal8'!BC27:BC35")
Range("'Yearly%'!AR39:AR47") = August
ElseIf Range("'Goal1'!E5") = "September" Then
September = Range("'Goal1'!BC9:BC17")
Range("'Yearly%'!C7:C15") = September
September = Range("'Goal1'!BC27:BC35")
Range("'Yearly%'!C16:C24") = September
September = Range("'Goal2'!BC9:BC17")
Range("'Yearly%'!Q7:Q15") = September
September = Range("'Goal2'!BC27:BC35")
Range("'Yearly%'!Q16:Q24") = September
September = Range("'Goal3'!BC9:BC17")
Range("'Yearly%'!AE7:AE15") = September
September = Range("'Goal3'!BC27:BC35")
Range("'Yearly%'!AE16:AE24") = September
September = Range("'Goal4'!BC9:BC17")
Range("'Yearly%'!AS70:AS15") = September
September = Range("'Goal4'!BC27:BC35")
Range("'Yearly%'!AS16:AS24") = September
September = Range("'Goal5'!BC9:BC17")
Range("'Yearly%'!C30:C38") = September
September = Range("'Goal5'!BC27:BC35")
Range("'Yearly%'!C39:C47") = September
September = Range("'Goal6'!BC9:BC17")
Range("'Yearly%'!Q30:Q38") = September
September = Range("'Goal6'!BC27:BC35")
Range("'Yearly%'!Q39:Q47") = September
September = Range("'Goal7'!BC9:BC17")
Range("'Yearly%'!AE30:AE38") = September
September = Range("'Goal7'!BC27:BC35")
Range("'Yearly%'!AE39:AE47") = September
September = Range("'Goal8'!BC9:BC17")
Range("'Yearly%'!AS30:AS38") = September
September = Range("'Goal8'!BC27:BC35")
Range("'Yearly%'!AS39:AS47") = September
ElseIf Range("'Goal1'!E5") = "October" Then
October = Range("'Goal1'!BC9:BC17")
Range("'Yearly%'!D715") = October
October = Range("'Goal1'!BC27:BC35")
Range("'Yearly%'!D1624") = October
October = Range("'Goal2'!BC9:BC17")
Range("'Yearly%'!R7:R15") = October
October = Range("'Goal2'!BC27:BC35")
Range("'Yearly%'!R16:R24") = October
October = Range("'Goal3'!BC9:BC17")
Range("'Yearly%'!AF7:AF15") = October
October = Range("'Goal3'!BC27:BC35")
Range("'Yearly%'!AF16:AF24") = October
October = Range("'Goal4'!BC9:BC17")
Range("'Yearly%'!AT30:AT38") = October
October = Range("'Goal4'!BC27:BC35")
Range("'Yearly%'!AT39:AT47") = October
October = Range("'Goal5'!BC9:BC17")
Range("'Yearly%'!D3038") = October
October = Range("'Goal5'!BC27:BC35")
Range("'Yearly%'!D3947") = October
October = Range("'Goal6'!BC9:BC17")
Range("'Yearly%'!R30:R38") = October
October = Range("'Goal6'!BC27:BC35")
Range("'Yearly%'!R39:R47") = October
October = Range("'Goal7'!BC9:BC17")
Range("'Yearly%'!AF30:AF38") = October
October = Range("'Goal7'!BC27:BC35")
Range("'Yearly%'!AF39:AF47") = October
October = Range("'Goal8'!BC9:BC17")
Range("'Yearly%'!AT30:AT38") = October
October = Range("'Goal8'!BC27:BC35")
Range("'Yearly%'!AT39:AT47") = October
End If
End Sub

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Preschool Mike]

I'll try.
--
Mike Mast
Special Education Preschool Teacher

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[JLatham]

One replacement for a multiple If..ElseIf...ElseIf...Else...End If block is
Select Case ... End Select. It offers the advantage of being a little
easier to read and is a bit more speedy in processing. Here's a dummy
example:

Select Case Range("'Goal1'!E5")
Case Is = "August"
'...code for what to for August
Case Is = "September"
'...code
'...to
'...deal with
'...September
Case Is = "November"
'...more code to
'...handle November
Case Is = "October"
'... you can have
'... any number of
'... Case Is =
'... statements, plus
'... the
Case Else
'this is not required
'but can be used as
'an error handler or
'catch-all
End Select

 

[Don Guillett]

One way that could be further refined.

Sub CollectandEnterFirstQuarterSAS()
Select Case LCase(Range("'Goal1'!E5"))
Case "august": c = 2 'type month in lower case
Case "september": c = 3
Case "october": c = 4
Case Else
End Select
'MsgBox c
Sheets("Yearly%").Cells(7, c).Resize(9).Value = _
Sheets("Goal1").Cells(9, "bc").Resize(9).Value
Sheets("Yearly%").Cells(16, c).Resize(9).Value = _
Sheets("Goal1").Cells(27, "bc").Resize(9).Value
'goal2
Sheets("Yearly%").Cells(7, c + 14).Resize(9).Value = _
Sheets("Goal2").Cells(9, "bc").Resize(9).Value
Sheets("Yearly%").Cells(16, c + 14).Resize(9).Value = _
Sheets("Goal2").Cells(27, "bc").Resize(9).Value
'goal3
Sheets("Yearly%").Cells(7, c + 28).Resize(9).Value = _
Sheets("Goal3").Cells(9, "bc").Resize(9).Value
Sheets("Yearly%").Cells(16, c + 28).Resize(9).Value = _
Sheets("Goal3").Cells(27, "bc").Resize(9).Value
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I'll try.