Generally speaking, which is faster?

[Steph]

I have the following code that selects a bunch of sheets, and then
copies a row and pastes the formula in that row to a set range. This
code takes a while to run. Generally speaking, would a for next loop
be faster? Or is there an even faster way (ie a do while loop?)
Thanks!

Set shtarray = Sheets Array "Sheet1", "Sheet2", "Sheet3", "Sheet4",
"Sheet5", "Sheet6"))

shtarray.Select
Sheets("Sheet1").Activate

'Copy Formula
Range("A2:EC2").Copy

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

frng.Select
Selection.PasteSpecial Paste:=xlPasteFormulas

 

[Bob Phillips]

Steph,

I can't see how this is copying from more than just Sheet1.

Generally speaking, a loop is a loop, Do While, Do Until, For Next, it is
unlikely that any will be faster than another to any significance.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steph]

Hi Bob. Thaks for the response. As for my crappy code, it started with the
macro recorder. So code aside, I selected several sheets using the ctl key,
then copied a row with formulas in it (each sheet has a row of formulas in
the same row), then copied it to a range. It works....just slowly.

So that being said, do you think a loop will be faster than my crappy-code
method?

I'm no great coder, so I'm just looking for a bit of reassurance before I
try to tackle the writing of the loop!!

Thanks again!

 

[Bob Phillips]

Steph,

The point I was making was that I couldn't comment as to whether a loop
would be quicker than your code, as I couldn't see how your code worked
through all the sheets, it only seemed to do one to me.

How does you code process all the sheets?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steph]

Hi Bob. That's odd, it works on all sheets in the array for me. Perhaps I
copied the sample code into the moriginal message incorrectly. I pasted it
again below. Thanks for your time!

Sub Pop_Forecast()

Dim shtarray As Sheets
Dim frng As Range
Dim sh As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

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

shtarray.Select
Sheets("Sheet1").Activate

'Copy Formula - every sheet has a formula in each cell of this row
Range("A2:EC2").Copy

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

frng.Select
Selection.PasteSpecial Paste:=xlPasteFormulas

Application.Calculation = xlCalculationAutomatic

 

[Bob Phillips]

Steph,

I actually tested it now, and see what you are doing. As such, I don't think
that a loop will be quicker than the way you are doing it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)