Generally speaking, which is faster?
 

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

Generally speaking, which is faster?
 

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

Generally speaking, which is faster?
 

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

Generally speaking, which is faster?
 

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

Generally speaking, which is faster?
 

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

Generally speaking, which is faster?
 

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)

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