Condensing Repetitive Code Distributed to Multiple Sheets

[inwalkedbud]

I need help condensing this repeating code. Thanks.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/1/2007 by Janet
Sheets("H").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!f4", "=data!f5", "=data!f6", "=data!f7", "=data!f8", "=data!f9", "=data!f10", "=data!f11", "=data!f12", "=data!f13", _
                                "=data!f15", "=data!f16", "=data!f17", "=data!f18", "=data!f19", "=data!f20", "=data!f21", "=data!f22", "=data!f23", "=data!f24", _
                                "=data!f25", "=data!f26", "=data!f27", "=data!f28", "=data!f29", "=data!f30", "=data!f31", "=data!f32", "=data!f33", "=data!f34", _
                                "=data!f35", "=data!f36", "=data!f37", "=data!f38", "=data!f39", "=data!f40", "=data!f41", "=data!f42", "=data!f43", "=data!f44", _
                                "=data!f45", "=data!f46", "=data!f47", "=data!f48", "=data!f49", "=data!f50", "=data!f51", "=data!f52", "=data!f53", "=data!f54", _
                                "=data!f55", "=data!f56", "=data!f57", "=data!f58", "=data!f59", "=data!f60", "=data!f61", "=data!f62", "=data!f63", "=data!f64", _
                                "=data!f65", "=data!f66", "=data!f67", "=data!f68", "=data!f69", "=data!f70", "=data!f71", "=data!f72", "=data!f73", "=data!f74", _
                                "=data!f75", "=data!f76", "=data!f77", "=data!f78", "=data!f79", "=data!f80", "=data!f81", "=data!f82", "=data!f83", "=data!f84", _
                                "=data!f85", "=data!f86", "=data!f87", "=data!f88", "=data!f89", "=data!f90", "=data!f91", "=data!f92", "=data!f93", "=data!f94", _
                                "=data!f95", "=data!f96", "=data!f97", "=data!f98", "=data!f99", "=data!f100", "=data!f101", "=data!f102", "=data!f103", "=data!f104")
Sheets("L").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!g4", "=data!g5", "=data!g6", "=data!g7", "=data!g8", "=data!g9", "=data!g10", "=data!g11", "=data!g12", "=data!g13", _
                                "=data!g15", "=data!g16", "=data!g17", "=data!g18", "=data!g19", "=data!g20", "=data!g21", "=data!g22", "=data!g23", "=data!g24", _
                                "=data!g25", "=data!g26", "=data!g27", "=data!g28", "=data!g29", "=data!g30", "=data!g31", "=data!g32", "=data!g33", "=data!g34", _
                                "=data!g35", "=data!g36", "=data!g37", "=data!g38", "=data!g39", "=data!g40", "=data!g41", "=data!g42", "=data!g43", "=data!g44", _
                                "=data!g45", "=data!g46", "=data!g47", "=data!g48", "=data!g49", "=data!g50", "=data!g51", "=data!g52", "=data!g53", "=data!g54", _
                                "=data!g55", "=data!g56", "=data!g57", "=data!g58", "=data!g59", "=data!g60", "=data!g61", "=data!g62", "=data!g63", "=data!g64", _
                                "=data!g65", "=data!g66", "=data!g67", "=data!g68", "=data!g69", "=data!g70", "=data!g71", "=data!g72", "=data!g73", "=data!g74", _
                                "=data!g75", "=data!g76", "=data!g77", "=data!g78", "=data!g79", "=data!g80", "=data!g81", "=data!g82", "=data!g83", "=data!g84", _
                                "=data!g85", "=data!g86", "=data!g87", "=data!g88", "=data!g89", "=data!g90", "=data!g91", "=data!g92", "=data!g93", "=data!g94", _
                                "=data!g95", "=data!g96", "=data!g97", "=data!g98", "=data!g99", "=data!g100", "=data!g101", "=data!g102", "=data!g103", "=data!g104")
Sheets("C").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!d4", "=data!d5", "=data!d6", "=data!d7", "=data!d8", "=data!d9", "=data!d10", "=data!d11", "=data!d12", "=data!d13", _
                                "=data!d15", "=data!d16", "=data!d17", "=data!d18", "=data!d19", "=data!d20", "=data!d21", "=data!d22", "=data!d23", "=data!d24", _
                                "=data!d25", "=data!d26", "=data!d27", "=data!d28", "=data!d29", "=data!d30", "=data!d31", "=data!d32", "=data!d33", "=data!d34", _
                                "=data!d35", "=data!d36", "=data!d37", "=data!d38", "=data!d39", "=data!d40", "=data!d41", "=data!d42", "=data!d43", "=data!d44", _
                                "=data!d45", "=data!d46", "=data!d47", "=data!d48", "=data!d49", "=data!d50", "=data!d51", "=data!d52", "=data!d53", "=data!d54", _
                                "=data!d55", "=data!d56", "=data!d57", "=data!d58", "=data!d59", "=data!d60", "=data!d61", "=data!d62", "=data!d63", "=data!d64", _
                                "=data!d65", "=data!d66", "=data!d67", "=data!d68", "=data!d69", "=data!d70", "=data!d71", "=data!d72", "=data!d73", "=data!d74", _
                                "=data!d75", "=data!d76", "=data!d77", "=data!d78", "=data!d79", "=data!d80", "=data!d81", "=data!d82", "=data!d83", "=data!d84", _
                                "=data!d85", "=data!d86", "=data!d87", "=data!d88", "=data!d89", "=data!d90", "=data!d91", "=data!d92", "=data!d93", "=data!d94", _
                                "=data!d95", "=data!d96", "=data!d97", "=data!d98", "=data!d99", "=data!d100", "=data!d101", "=data!d102", "=data!d103", "=data!d104")
End Sub

[Norie]

Try this.

Dim I As Long


For I = 4 To 104
    Sheets("H").Range("C3").Offset(, I - 4) = "=data!F" & I
    Sheets("L").Range("C3").Offset(, I - 4) = "=data!H" & I
    Sheets("C").Range("C3").Offset(, I - 4) = "=data!D" & I
Next I

By the way do you really need the formulas?

If you just want the values this could be done with a simple copy and paste special using the transpose option.

[inwalkedbud]

Thank you, Norie--

Your code worked to perfection. In fact, I did need the formulas in this case.

inwalkedbud

[inwalkedbud]

Thanks shg--your code worked wonderful too.

[shg]

Or like this:

    Sheets("H").Range("C3").Resize(, 100).FormulaArray = "=Transpose(data!F4:F103)"
    Sheets("L").Range("C3").Resize(, 100).FormulaArray = "=Transpose(data!G4:F103)"
    Sheets("C").Range("C3").Resize(, 100).FormulaArray = "=Transpose(data!D4:D103)"