More efficient way?

[Steph]

Hi everyone. Was hoping someone had some ideas on how to speed up the
following code. The base code was started using the macro-recorder. I
selected several sheets using the ctl key,
then copied row 2 with formulas in it (each sheet has a row of formulas in
the same row), then pasted it to a range. It works....just slowly. I'm
running a 2.4G processor with 2.0G of memory...and it still takes forever!
Granted, each sheet is being populated with 13,000 cells, but I still didn't
think it would take this long. Have a better method?? Thanks!


Sub Forecast()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv
Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))

shtarray.Select
Sheets("LMU").Activate

Range("A2:EC2").Copy

Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row)
frng.PasteSpecial Paste:=xlPasteFormulas

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[Don Guillett]

I don't know if this is what you want but this will copy row 2 formulas in
each sheet down to the last cell in col B of each worksheet.

Sub fc()
Set shtarray = Sheets(Array("LMU", "Kit", "SMLC"))
For Each sh In shtarray
With sh
x = .Cells(Rows.Count, "B").End(xlUp).Row
..Rows(2).Copy .Rows("2:" & x)
End With
Next
End Sub

 

[Steph]

Thanks for the response Don. That does exactly what I wanted done. But
unfortunately, it's no faster than a similar For Next loop I have....as a
matter of fact, the timing of the 2 procedures are almost identical! Oh
well, Thanks again!

 

[Don Guillett]

try using the autofill idea
Range("f2:i2").AutoFill Destination:=Range("f2:i6")

 

[Steph]

Hi Don,

Thanks for the new suggestion. I edited your piece of code to fit my range,
and I got an Autofill method of range class failed error. Is that because
my formula is in row 2, and I don't want the autofill to start until row 5?
My code is :

Set shtarray = Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

For Each sh In shtarray

Range("A2:EC2").AutoFill Destination:=Range("A5:EC" &
Data.Range("B65536").End(xlUp).Row)

With sh.Range("A5:EC" & Data.Range("B65536").End(xlUp).Row)
.Value = .Value
End With
Next sh

Thanks again Don!!

 

[Don Guillett]

Yep. Never hurts to highlight the word and touch the F1 key. Amazing what
you get. Maybe you could clear/hide the interveing rows after the fill.

AutoFill Method
See Also Applies To Example Specifics
Performs an autofill on the cells in the specified range. Variant.

expression.AutoFill(Destination, Type)

expression Required. An expression that returns one of the objects in the
Applies To list.

Destination Required Range object. The cells to be filled. The destination
must include the source range.

Type Optional XlAutoFillType. Specifies the fill type.

XlAutoFillType can be one of these XlAutoFillType constants.
xlFillDays
xlFillFormats
xlFillSeries
xlFillWeekdays
xlGrowthTrend
xlFillCopy
xlFillDefault default
xlFillMonths
xlFillValues
xlFillYears
xlLinearTrend
If this argument is xlFillDefault or omitted, Microsoft Excel selects
the most appropriate fill type, based on the source range.

Example
This example performs an autofill on cells A1:A20 on Sheet1, based on the
source range A1:A2 on Sheet1. Before running this example, type 1 in cell A1
and type 2 in cell A2.

Set sourceRange = Worksheets("Sheet1").Range("A1:A2")
Set fillRange = Worksheets("Sheet1").Range("A1:A20")
sourceRange.AutoFill Destination:=fillRange

 

[Steph]

Thanks for all the info Don. I'll try adding making the formula row
directly above the autofill range. The small change to my workbook
structure will be well worth it if autofill speeds the code up! If not, oh
well.....the 'ol college try! Thanks again for all your help!!