Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA error - run-time error '1004':
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA error - run-time error '1004':
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA error - run-time error '1004':
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. -- Don Guillett SalesAid Software "Romanian37 " wrote in message ... Thanks for the tip - couldn't get the 'with' statement to work out but 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? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Run time error 1004 | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Run-time Error 1004, Please Help | Excel Programming |