VBA error - run-time error '1004':

[Romanian37]

All,

I've been modifying a set of macros to filter a data set and pu
resulting data in a number of different sheets. Each of these sheet
have a number of formulas in row 2 that I'm copying and pastespecial
formulas for the entire data set. This data is then copied an
pastespecial values done to save memory.

Even though the modifications were not made to the code below (all cod
changed is done after the code below), two errors seems to come up.

"Run time error : information cannot be pasted becasue copy and past
area are not the same size and shape" or
"Run time error 1004: PasteSpecial method of range failed"

I've tried a few things: Added cutcopymode = false to clear clipboar
(thought it could be memory?), also changed code from selecting on
cell (Cells(3, 19)) to selecting entire range again (see below)

There are a number of copies and pastes like this in the macros, whic
seems to work fine, also the original macro (without the addition o
filtering and copying and pasting to new sheets), seems to work fin
over identical code


I can't see the problem, unless its hardware??

Any help would be much appreciated.

Thanks

Will



Range(Cells(3, 19), Cells(MasterEnd, 19)).Select
Selection.PasteSpecial Paste:=xlFormulas

'Copies the resulting values
Range(Cells(3, 19), Cells(MasterEnd, 19)).Copy
'Pastes these values over the top of formulas
' ERROR APPEARS ON RUNNING LINE BELOW
Range(Cells(3, 19), Cells(MasterEnd, 19)).PasteSpecial Paste:=xlValues
Application.CutCopyMode = Fals

 

[Don Guillett]

why not just do this?

with Range(Cells(3, 19), Cells(MasterEnd, 19))
.value=.formula
end with

 

[Romanian37]

Thanks for the tip - couldn't get the 'with' statement to work out bu
the below did

Range(Cells(3, 19), Cells(MasterEnd, 19)) = Range(Cells(3, 19)
Cells(MasterEnd, 19)).Value


Whats the advantage of using the 'with' statement

 

[Don Guillett]

try it with this instead.
..formula=.value

ease of writing & this from vbe HELP
With Statement
Executes a series of statements on a single object or a user-defined type.

Syntax

With object
[statements]

End With

The With statement syntax has these parts:

Part Description
object Required. Name of an object or a user-defined type.
statements Optional. One or more statements to be executed on object.



Remarks

The With statement allows you to perform a series of statements on a
specified object without requalifying the name of the object. For example,
to change a number of different properties on a single object, place the
property assignment statements within the With control structure, referring
to the object once instead of referring to it with each property assignment.
The following example illustrates use of the With statement to assign values
to several properties of the same object.

With MyLabel
.Height = 2000
.Width = 2000
.Caption = "This is MyLabel"
End With
Note Once a With block is entered, object can't be changed. As a result,
you can't use a single With statement to affect a number of different
objects.

You can nest With statements by placing one With block within another.
However, because members of outer With blocks are masked within the inner
With blocks, you must provide a fully qualified object reference in an inner
With block to any member of an object in an outer With block.

Note In general, it's recommended that you don't jump into or out of With
blocks. If statements in a With block are executed, but either the With or
End With statement is not executed, a temporary variable containing a
reference to the object remains in memory until you exit the procedure.