I'm trying to figure out how to write this using advanced vba coding. The original way i wrote this code was extremely large and repetitive. I'm wondering if someone can show me an example of how to create a ".Range formula" this loops thru 13 identical worksheets. This worksheet will be created every month, and also the past month totals (if exist) will need to be retrieved and pulled over to the current month spreadsheet worksheets and the Summary worksheet of all 13 worksheets.
I have a idea of my coding its the if statement where i get hung up yet, because i don't understand how to write the range formula for what i want to do.

here is the ranges of the totals i want to start out capturing:
B4:J4; B7:J7; D12; B19:M19; ETC

this is consistent on all 13 worksheets. Here's the code I've written thus far:

Private Sub CommandButton1_Click()
Dim vDate
Dim vLinkPath
Dim vMonth1
Dim vYear1
Dim vStatus

vDate = ActiveSheet.Range("A1")
vLinkPath = ActiveSheet.Range("A13")

If IsDate(vDate) = False Then
    MsgBox "You must first enter an Date for this workbook in cell A1.", vbExclamation, "Missing Data"
    Range("A1").Select
    Exit Sub
End If

If IsEmpty(vLinkPath) = True Then
    MsgBox "You must first enter an Archive File Directory for Education Monthly Report in cell A13.", vbExclamation, "Missing Data"
    Range("A13").Select
    Exit Sub
End If

vMonth1 = MonthName(Month(DateAdd("m", -1, vDate)))
vYear1 = Format(DateAdd("m", -1, vDate), "yyyy")
If Month(vDate) = 1 Or Month(vDate) = 6 Then
    'this is the area where I add my logic and this is the area i need help with"
'i have a lot of variables which is too large to put here.  Some i'm wondering is there an advanced way i can dim the variables like with a range or array that loops thru 13 identical worksheets. 

Else
	'this area should grab all year-to-date totals from the previous month and apply to the current month total that will be entered. 
	'below is what i had before when i was using dim variables.  Is there a way i can use a range to loop thru all 13 worksheets to pull totals over to the current month spreadsheet?'
    vBCCB4 = "=B2+'" + vLinkPath + vYear1 + "\[" + vMonth1 + " " + vYear1 + ".xls]BCC'!$B$4"
End If

vStatus = "Links to external workbooks are set, based on " + MonthName(Month(vDate)) + " " + Format(vDate, "yyyy") + "."
Sheets("BCC").Unprotect
Sheets("BCFC").Unprotect
Sheets("EKCC").Unprotect
Sheets("FCDC").Unprotect
Sheets("GRCC").Unprotect
Sheets("KCIW").Unprotect
Sheets("KSP").Unprotect
Sheets("KSR").Unprotect
Sheets("LEA").Unprotect
Sheets("LLCC").Unprotect
Sheets("LSCC").Unprotect
Sheets("MAC").Unprotect
Sheets("NTC").Unprotect
Sheets("OCCC").Unprotect
Sheets("RCC").Unprotect
Sheets("WKCC").Unprotect
Sheets("MASTER").Unprotect
With Sheets("BCC")
    .Range("B4") = vBCCB4
    .Range("C4") = vBCCC4
    .Range("D4") = vBCCD4
    .Range("E4") = vBCCE4
    .Range("F4") = vBCCF4
    .Range("G4") = vBCCG4
    .Range("H4") = vBCCH4
    .Range("I4") = vBCCI4
    .Range("J4") = vBCCJ4
    .Range("K4") = vBCCK4
    .Range("B7") = vBCCB7
    .Range("C7") = vBCCC7
    .Range("D7") = vBCCD7
    .Range("E7") = vBCCE7
    .Range("F7") = vBCCF7
    .Range("G7") = vBCCG7
    .Range("H7") = vBCCH7
    .Range("I7") = vBCCI7
    .Range("J7") = vBCCJ7
    .Range("K7") = vBCCK7
    .Range("D12") = vBCCD12
    .Range("G12") = vBCCG12
    .Range("B19") = vBCCB19
    .Range("C19") = vBCCC19
    .Range("D19") = vBCCD19
    .Range("E19") = vBCCE19
    .Range("F19") = vBCCF19
    .Range("G19") = vBCCG19
    .Range("H19") = vBCCH19
    .Range("I19") = vBCCI19
    .Range("J19") = vBCCJ19
    .Range("K19") = vBCCK19
    .Range("L19") = vBCCL19
    .Range("M19") = vBCCM19
    .Range("N19") = vBCCN19
    .Range("B23") = vBCCB23
    .Range("C23") = vBCCC23
    .Range("D23") = vBCCD23
    .Range("E23") = vBCCE23
    .Range("F23") = vBCCF23
    .Range("G23") = vBCCG23
    .Range("H23") = vBCCH23
    .Range("I23") = vBCCI23
    .Range("J23") = vBCCJ23
    .Range("K23") = vBCCK23
    .Range("L23") = vBCCL23
    .Range("M23") = vBCCM23
    .Range("N23") = vBCCN23
    .Range("B25") = vBCCB25
    .Range("C25") = vBCCC25
    .Range("D25") = vBCCD25
    .Range("E25") = vBCCE25
    .Range("F25") = vBCCF25
    .Range("G25") = vBCCG25
    .Range("H25") = vBCCH25
    .Range("I25") = vBCCI25
    .Range("J25") = vBCCJ25
    .Range("K25") = vBCCK25
    .Range("L25") = vBCCL25
    .Range("M25") = vBCCM25
    .Range("N25") = vBCCN25
    .Range("B27") = vBCCB27
    .Range("C27") = vBCCC27
    .Range("D27") = vBCCD27
    .Range("E27") = vBCCE27
    .Range("F27") = vBCCF27
    .Range("G27") = vBCCG27
    .Range("H27") = vBCCH27
    .Range("I27") = vBCCI27
    .Range("J27") = vBCCJ27
    .Range("K27") = vBCCK27
    .Range("L27") = vBCCL27
    .Range("M27") = vBCCM27
    .Range("N27") = vBCCN27
    .Range("B29") = vBCCB29
    .Range("C29") = vBCCC29
    .Range("D29") = vBCCD29
    .Range("E29") = vBCCE29
    .Range("F29") = vBCCF29
    .Range("G29") = vBCCG29
    .Range("H29") = vBCCH29
    .Range("I29") = vBCCI29
    .Range("J29") = vBCCJ29
    .Range("K29") = vBCCK29
    .Range("L29") = vBCCL29
    .Range("M29") = vBCCM29
    .Range("N29") = vBCCN29
    .Range("B31") = vBCCB31
    .Range("C31") = vBCCC31
    .Range("D31") = vBCCD31
    .Range("E31") = vBCCE31
    .Range("F31") = vBCCF31
    .Range("G31") = vBCCG31
    .Range("H31") = vBCCH31
    .Range("I31") = vBCCI31
    .Range("J31") = vBCCJ31
    .Range("K31") = vBCCK31
    .Range("L31") = vBCCL31
    .Range("M31") = vBCCM31
    .Range("N31") = vBCCN31
    .Range("B33") = vBCCB33
    .Range("C33") = vBCCC33
    .Range("D33") = vBCCD33
    .Range("E33") = vBCCE33
    .Range("F33") = vBCCF33
    .Range("G33") = vBCCG33
    .Range("H33") = vBCCH33
    .Range("I33") = vBCCI33
    .Range("J33") = vBCCJ33
    .Range("K33") = vBCCK33
    .Range("L33") = vBCCL33
    .Range("M33") = vBCCM33
    .Range("N33") = vBCCN33
    .Range("B35") = vBCCB35
    .Range("C35") = vBCCC35
    .Range("D35") = vBCCD35
    .Range("E35") = vBCCE35
    .Range("F35") = vBCCF35
    .Range("G35") = vBCCG35
    .Range("H35") = vBCCH35
    .Range("I35") = vBCCI35
    .Range("J35") = vBCCJ35
    .Range("K35") = vBCCK35
    .Range("L35") = vBCCL35
    .Range("M35") = vBCCM35
    .Range("N35") = vBCCN35
    .Range("B40") = vBCCB40
    .Range("C40") = vBCCC40
    .Range("D40") = vBCCD40
    .Range("E40") = vBCCE40
    .Range("F40") = vBCCF40
    .Range("G40") = vBCCG40
    .Range("H40") = vBCCH40
    .Range("I40") = vBCCI40
    .Range("J40") = vBCCJ40
    .Range("K40") = vBCCK40
    .Range("L40") = vBCCL40
    .Range("M40") = vBCCM40
    .Range("N40") = vBCCN40
    .Range("B49") = vBCCB49
    .Range("C49") = vBCCC49
    .Range("D49") = vBCCD49
    .Range("E49") = vBCCE49
    .Range("F49") = vBCCF49
End With


Sheets("BCC").Protect DrawingObjects:=True
Sheets("BCFC").Protect DrawingObjects:=True
Sheets("EKCC").Protect DrawingObjects:=True
Sheets("FCDC").Protect DrawingObjects:=True
Sheets("GRCC").Protect DrawingObjects:=True
Sheets("KCIW").Protect DrawingObjects:=True
Sheets("KSP").Protect DrawingObjects:=True
Sheets("KSR").Protect DrawingObjects:=True
Sheets("LEA").Protect DrawingObjects:=True
Sheets("LLCC").Protect DrawingObjects:=True
Sheets("LSCC").Protect DrawingObjects:=True
Sheets("MAC").Protect DrawingObjects:=True
Sheets("NTC").Protect DrawingObjects:=True
Sheets("OCCC").Protect DrawingObjects:=True
Sheets("RCC").Protect DrawingObjects:=True
Sheets("WKCC").Protect DrawingObjects:=True
Sheets("MASTER").Protect DrawingObjects:=True
ActiveSheet.Unprotect
With ActiveSheet
    .Range("A18") = vStatus
    .Range("A18").Font.ColorIndex = 0
    .Range("A18").Interior.ColorIndex = 15
End With
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True
MsgBox vStatus, vbInformation, "Setup Complete"

End Sub