Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel |