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" wrote in
message ...

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