Can't get .value=.value to work
 

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

Can't get .value=.value to work
 

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

Can't get .value=.value to work
 

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

Can't get .value=.value to work
 

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

Can't get .value=.value to work
 

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!




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