Can't get .value=.value to work

[Steph]

Can anyone see why the "range value" section of this code won't work? I get
an object not defined error?

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

For Each sh In ActiveWorkbook.Worksheets(shtarray)
sh.Range("A2:EC2").Copy

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

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

 

[Bob Phillips]

Why don't you just use xlPasteValues rather than xlPasteFormulas, you won't
need the other bit then.

--

HTH

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

 

[Steph]

Hi Bob,

Because I'm copying a line of formulas that I need carried out. Then to
make the file smaller and hopefully the code run faster, I wanted to take
the formulated value and hard code it.

Using PasteValues will populate all my sheets with zero's.

-Steph

 

[Bob Phillips]

OK Steph,

Give this a try. Not comprehensively tested, but it ran through for me

shtArray = Array("Sheet1", "Sheet5", "Sheet2", "Sheet3", "Sheet4")

For Each sh In ActiveWorkbook.Worksheets(shtArray)
sh.Range("A2:EC2").Copy

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

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


--

HTH

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

 

[Steph]

Hi Bob,

It woked! Thanks so much! Now if I can only speed it up a bit!! Still
takes several minutes to run. Thanks again!