Simplify the Macro of Looping

Hi,

Anyone can help me to simplify the below macro.

I have a lot of repeating until wZ as variable.

Thank you.


Sub Macro1()

Dim wA, wZ As String
Windows("SummaryLeakFreq").Activate
Sheet1.Select
wA = Range("C4").Value
wB = Range("C5").Value
wC = Range("C6").Value
wD = Range("C7").Value
wE = Range("C8").Value

Sheets("LeakFrequencySummary").Select

'Failure Cases
Range("C7").Select
ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$B$6"

'Freq
Range("D7").Select
ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$D$39"
'Pin
Range("E7").Select
ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$E$39"

'Small
Range("F7").Select
ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$F$39"

'Medium
Range("G7").Select
ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$G$39"

'Large
Range("H7").Select
ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$H$39"


'Failure Cases
Range("C8").Select
ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$B$6"

'Freq
Range("D8").Select
ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$D$39"
'Pin
Range("E8").Select
ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$E$39"

'Small
Range("F8").Select
ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$F$39"

'Medium
Range("G8").Select
ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$G$39"

'Large
Range("H8").Select
ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$H$39"

'Failure Cases
Range("C9").Select
ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$B$6"

'Freq
Range("D9").Select
ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$D$39"
'Pin
Range("E9").Select
ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$E$39"

'Small
Range("F9").Select
ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$F$39"

'Medium
Range("G9").Select
ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$G$39"

'Large
Range("H9").Select
ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$H$39"

'Failure Cases
Range("C10").Select
ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$B$6"

'Freq
Range("D10").Select
ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$D$39"
'Pin
Range("E10").Select
ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$E$39"

'Small
Range("F10").Select
ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$F$39"

'Medium
Range("G10").Select
ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$G$39"

'Large
Range("H10").Select
ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$H$39"


'Failure Cases
Range("C11").Select
ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$B$6"

'Freq
Range("D11").Select
ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$D$39"
'Pin
Range("E11").Select
ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$E$39"

'Small
Range("F11").Select
ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$F$39"

'Medium
Range("G11").Select
ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$G$39"

'Large
Range("H11").Select
ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$H$39"



End Sub

Sub Macro1()

Dim wA, wZ As String
Dim i As Long
Dim aryW(1 To 5)
'Windows("SummaryLeakFreq").Activate
Sheet1.Select
aryW(1) = Range("C4").Value
aryW(2) = Range("C5").Value
aryW(3) = Range("C6").Value
aryW(4) = Range("C7").Value
aryW(5) = Range("C8").Value

With Sheets("LeakFrequencySummary")
For i = 7 To 11
'Failure Cases
.Range("C" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6"
'Freq
.Range("D" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$D$39"
'Pin
.Range("E" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$E$39"
'Small
.Range("F" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$F$39"
'Medium
.Range("G" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$G$39"
'Large
.Range("H" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$H$39"
Next i
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ccl28" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Anyone can help me to simplify the below macro.
>
> I have a lot of repeating until wZ as variable.
>
> Thank you.
>
>
> Sub Macro1()
>
> Dim wA, wZ As String
> Windows("SummaryLeakFreq").Activate
> Sheet1.Select
> wA = Range("C4").Value
> wB = Range("C5").Value
> wC = Range("C6").Value
> wD = Range("C7").Value
> wE = Range("C8").Value
>
> Sheets("LeakFrequencySummary").Select
>
> 'Failure Cases
> Range("C7").Select
> ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$B$6"
>
> 'Freq
> Range("D7").Select
> ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$D$39"
> 'Pin
> Range("E7").Select
> ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$E$39"
>
> 'Small
> Range("F7").Select
> ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$F$39"
>
> 'Medium
> Range("G7").Select
> ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$G$39"
>
> 'Large
> Range("H7").Select
> ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$H$39"
>
>
> 'Failure Cases
> Range("C8").Select
> ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$B$6"
>
> 'Freq
> Range("D8").Select
> ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$D$39"
> 'Pin
> Range("E8").Select
> ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$E$39"
>
> 'Small
> Range("F8").Select
> ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$F$39"
>
> 'Medium
> Range("G8").Select
> ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$G$39"
>
> 'Large
> Range("H8").Select
> ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$H$39"
>
> 'Failure Cases
> Range("C9").Select
> ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$B$6"
>
> 'Freq
> Range("D9").Select
> ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$D$39"
> 'Pin
> Range("E9").Select
> ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$E$39"
>
> 'Small
> Range("F9").Select
> ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$F$39"
>
> 'Medium
> Range("G9").Select
> ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$G$39"
>
> 'Large
> Range("H9").Select
> ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$H$39"
>
> 'Failure Cases
> Range("C10").Select
> ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$B$6"
>
> 'Freq
> Range("D10").Select
> ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$D$39"
> 'Pin
> Range("E10").Select
> ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$E$39"
>
> 'Small
> Range("F10").Select
> ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$F$39"
>
> 'Medium
> Range("G10").Select
> ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$G$39"
>
> 'Large
> Range("H10").Select
> ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$H$39"
>
>
> 'Failure Cases
> Range("C11").Select
> ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$B$6"
>
> 'Freq
> Range("D11").Select
> ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$D$39"
> 'Pin
> Range("E11").Select
> ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$E$39"
>
> 'Small
> Range("F11").Select
> ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$F$39"
>
> 'Medium
> Range("G11").Select
> ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$G$39"
>
> 'Large
> Range("H11").Select
> ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$H$39"
>
>
>
> End Sub
>
>
> --
> ccl28
> ------------------------------------------------------------------------
> ccl28's Profile:
http://www.excelforum.com/member.php...o&userid=36095
> View this thread: http://www.excelforum.com/showthread...hreadid=570248
>