Run Time error 1004 - Pastespecial method of range class failed.

Hi,

I am trying to programmatically copy the text contents of one cell 
(including formatting) on one worksheet to a merged range on another.

I get the error in the title when I run the macro initially... then 
strangely it works with no error after failing to run it a couple of times. 

My code is as follows:

Application.DisplayAlerts = False
 Application.ScreenUpdating = False
Sheets("admin").Select
    Range("A2").Select
    Selection.Copy
    Sheets("Submission Form").Select
    Range("D6:G8").Select
         End With
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, 
Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
      With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    End With
     Application.DisplayAlerts = True
     Application.ScreenUpdating = True
0
Utf
2/1/2010 12:38:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
2209 Views

Similar Articles

[PageSpeed] 59

Try the below..

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("admin").Range("A2").Copy
Sheets("Submission Form").Range("D6:G8").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
    
    With Selection
        .BorderAround (xlDouble)
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .MergeCells = True
    End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

-- 
Jacob


"oli merge" wrote:

> Hi,
> 
> I am trying to programmatically copy the text contents of one cell 
> (including formatting) on one worksheet to a merged range on another.
> 
> I get the error in the title when I run the macro initially... then 
> strangely it works with no error after failing to run it a couple of times. 
> 
> My code is as follows:
> 
> Application.DisplayAlerts = False
>  Application.ScreenUpdating = False
> Sheets("admin").Select
>     Range("A2").Select
>     Selection.Copy
>     Sheets("Submission Form").Select
>     Range("D6:G8").Select
>          End With
>     Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, 
> Operation:=xlNone _
>         , SkipBlanks:=False, Transpose:=False
>     Application.CutCopyMode = False
>     With Selection
>         .HorizontalAlignment = xlGeneral
>         .VerticalAlignment = xlBottom
>         .WrapText = True
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = False
>         .ReadingOrder = xlContext
>         .MergeCells = True
>       With Selection.Borders(xlEdgeLeft)
>         .LineStyle = xlDouble
>         .ColorIndex = 0
>         .TintAndShade = 0
>         .Weight = xlMedium
>     End With
>     With Selection.Borders(xlEdgeTop)
>         .LineStyle = xlDouble
>         .ColorIndex = 0
>         .TintAndShade = 0
>         .Weight = xlMedium
>     End With
>     With Selection.Borders(xlEdgeBottom)
>         .LineStyle = xlDouble
>         .ColorIndex = 0
>         .TintAndShade = 0
>         .Weight = xlMedium
>     End With
>     With Selection.Borders(xlEdgeRight)
>         .LineStyle = xlDouble
>         .ColorIndex = 0
>         .TintAndShade = 0
>         .Weight = xlMedium
>     End With
>     Selection.Borders(xlInsideVertical).LineStyle = xlNone
>     Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
>     With Selection
>         .HorizontalAlignment = xlGeneral
>         .VerticalAlignment = xlTop
>         .WrapText = True
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = False
>         .ReadingOrder = xlContext
>         .MergeCells = True
>     End With
>     End With
>      Application.DisplayAlerts = True
>      Application.ScreenUpdating = True
0
Utf
2/1/2010 12:49:01 PM
Perfect, thanks for tidying up my messy recorded code too!

"Jacob Skaria" wrote:

> Try the below..
> 
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> 
> Sheets("admin").Range("A2").Copy
> Sheets("Submission Form").Range("D6:G8").PasteSpecial Paste:=xlPasteAll
> Application.CutCopyMode = False
>     
>     With Selection
>         .BorderAround (xlDouble)
>         .HorizontalAlignment = xlGeneral
>         .VerticalAlignment = xlTop
>         .WrapText = True
>         .MergeCells = True
>     End With
> 
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> 
> -- 
> Jacob
> 
> 
> "oli merge" wrote:
> 
> > Hi,
> > 
> > I am trying to programmatically copy the text contents of one cell 
> > (including formatting) on one worksheet to a merged range on another.
> > 
> > I get the error in the title when I run the macro initially... then 
> > strangely it works with no error after failing to run it a couple of times. 
> > 
> > My code is as follows:
> > 
> > Application.DisplayAlerts = False
> >  Application.ScreenUpdating = False
> > Sheets("admin").Select
> >     Range("A2").Select
> >     Selection.Copy
> >     Sheets("Submission Form").Select
> >     Range("D6:G8").Select
> >          End With
> >     Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, 
> > Operation:=xlNone _
> >         , SkipBlanks:=False, Transpose:=False
> >     Application.CutCopyMode = False
> >     With Selection
> >         .HorizontalAlignment = xlGeneral
> >         .VerticalAlignment = xlBottom
> >         .WrapText = True
> >         .Orientation = 0
> >         .AddIndent = False
> >         .IndentLevel = 0
> >         .ShrinkToFit = False
> >         .ReadingOrder = xlContext
> >         .MergeCells = True
> >       With Selection.Borders(xlEdgeLeft)
> >         .LineStyle = xlDouble
> >         .ColorIndex = 0
> >         .TintAndShade = 0
> >         .Weight = xlMedium
> >     End With
> >     With Selection.Borders(xlEdgeTop)
> >         .LineStyle = xlDouble
> >         .ColorIndex = 0
> >         .TintAndShade = 0
> >         .Weight = xlMedium
> >     End With
> >     With Selection.Borders(xlEdgeBottom)
> >         .LineStyle = xlDouble
> >         .ColorIndex = 0
> >         .TintAndShade = 0
> >         .Weight = xlMedium
> >     End With
> >     With Selection.Borders(xlEdgeRight)
> >         .LineStyle = xlDouble
> >         .ColorIndex = 0
> >         .TintAndShade = 0
> >         .Weight = xlMedium
> >     End With
> >     Selection.Borders(xlInsideVertical).LineStyle = xlNone
> >     Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> >     With Selection
> >         .HorizontalAlignment = xlGeneral
> >         .VerticalAlignment = xlTop
> >         .WrapText = True
> >         .Orientation = 0
> >         .AddIndent = False
> >         .IndentLevel = 0
> >         .ShrinkToFit = False
> >         .ReadingOrder = xlContext
> >         .MergeCells = True
> >     End With
> >     End With
> >      Application.DisplayAlerts = True
> >      Application.ScreenUpdating = True
0
Utf
2/1/2010 1:43:01 PM
Just to note though, I had to use "xlPasteAllUsingSourceTheme" to keep my 
formatting (lots of bold and underlined words).

Thanks,

"oli merge" wrote:

> Perfect, thanks for tidying up my messy recorded code too!
> 

0
Utf
2/1/2010 1:46:02 PM
Reply:

Similar Artilces:

Restoring mailbox store fails
I've just installed a Windows Small Business Server which comes with Exchange 2003 Standard. The problem is that I can't restore mailboxes. What am I doing wrong? Here is how I try to do it: - Backup using ntbackup utility in advanced mode MS Exchange server\First Storage Group - Create Recovery Storage Goup and then Connect Database - Mailbox Store. After this I have First Storage Group with Mailbox Store mounted and Recovery Storage Group with Mailbox Store unmounted. Folloing step is restore Mailbox Store from backup. I run ntbackup and in Select what to restore mark First ...

Bad address error
Trying to send a message from our exchange server to my personal acct and I recieve a NDR notice " The recipient name is not recognized". I contacted my ISP and they state the problem is in house and not on there side. Need any help fixing this problem. This all started about 2 weeks ago. Thanks in advance, Ron T. What is the full text in the NDR? What changed about 2 weeks ago? What is the domain where the message is destined? Can you telnet to that domain's e-mail servers from your Exchange server on port 25? If so, can you manually send a message? -- Ben Winzen...

Permissions needed to run on Citrix?
Hello, GP version 9.1: We are trying to set up our GP install on our Citrix server. For some reason, only a domain user with local admin rights on the Citrix server is able to launch GP. Any other user attempting to launch GP receives the following errors (these begin immediately after the company is selected): Unhandled object exception: Access is denied. (OK is selected here) Unhandled object exception: Error calling method 'createProcessingInstruction' EXCEPTION_CLASS_OBJECT_EXCEPTION ExceptionSubClass:0 (OK is selected here) The page cannot be displayed The page y...

How do Load at Run Time DLL's work?
In the example of dll's loaded at runtime they use LoadLibrary and GetProcAddress. The LoadLibrary works for me, but the GetProcAddress doesn't. Well it did for a short time yesterday for no apparant reason, then it quit and wont work today. Anyway, when it was working, the parameter wasn't passed correctly to the dll function. So how does the compiler know what the parameter list is for the dll function? I don't have an h file included or anything to tell the calling program what the parameters can be. Here some some code from the dll In MEP.h class CMEPApp : public CWinApp...

Error of "application-defined or object-defined error" in Goalseek
Hi all, I always have above error while running below code in the excel 2007, please help me out! Sub GoalSeekTry() Source = Cells(8, 7) x = Cells(7, 7).Value ActiveSheet.Range(Source).GoalSeek Goal:=-800, ChangingCell:=ActiveSheet.Range(x) End With End Sub Source = X*X +X*300 Regards, Edward sorry adjust the code little bit by removing "End With" Sub GoalSeekTry() Source = Cells(8, 7) x = Cells(7, 7).Value ActiveSheet.Range(Source).GoalSeek Goal:=-800, ChangingCell:=ActiveSheet.Range(x) End Sub "Edward Wang" wrote: > Hi all, > I...

outlook 2000 doctor watson errors
When I try to edit a forwarded email containg HTML text outlook crashes giving an Dr. Watson Error. Any ideas. Thanks in advance. Error? Version of Outlook? -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox! www.sparnaaij.net (I changed my reply address; remove all CAPS and _underscores_ from the address when maili...

#Error in subform foreign key
Hi all, I am getting "#Error" in my foreign key id field for two subforms, but not the third. As far as I can tell each is setup in their respective tables similarly. I have tried deleting, changing thpe data type, I've even re-created the whole database (re-created suspect tables and imported everything else). Whenever I try to add values in any field on the subform I get "The value you entered isn't valid for this field..." I'm at a loss. Thanks in advance, Nick "NickX" <nicknnator@aol.com> wrote in message news:1176403733.363533.24828...

MSE 5.5 Event ID 1194 Accept clients on external interface POP3 failed
MSE 5.5 !! Internet Mail Service Connector fails to respond on port 110 with the following event log entries: ---------------------------------------------- Event ID 11507 (Configuration) Ready to accept clients on the POP3 interface. Event ID 11508 (Configuration) Accept clients on external interface POP3 failed with error 0x80004005. Event ID 1194 (General) Accept clients on POP3 interface failed in function HrRegisterProtocol with error 0x2740. ---------------------------------------------- Netstat -an command shows port 110: TCP 127.0.0.1:110 0.0.0.0 Listening -----------...

Outlook 2007 Receive Error
I have begun to receive the error message, "Receiving' reported error (0x80040154): 'Unknown Error 8s80040154'. And I cannot receive e-mails into my Outlook 2007. Any resolution available? Try this: click 'Start|Run' and enter the following: regsvr32 inetcomm.dll Click OK. Please let us know if this works. Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imaging -- WA7BGX http://www.kvoa.com -- "When News breaks, we fix it!" KVOA Television, Tucson, AZ. NBC Channel 4 Live at Hot Licks - www.badnewsbluesband.com &qu...

WIN32 Error
I cannot access any programs or iinstall anything i am on windows xp pro sp3 Email Me on Mikeyjjgurden@hotmail.co.uk "Michael" <mikeyjjgurden@hotmail.co.uk> wrote in message news:eBK7hy02KHA.5588@TK2MSFTNGP06.phx.gbl... > I cannot access any programs or iinstall anything i am on windows xp pro > sp3 > > Email Me on Mikeyjjgurden@hotmail.co.uk > Try asking in a Windows XP discussion group. This group is for the Microsoft relational database product, Access. Try this to find a Windows XP group: http://www.microsoft.com/communities/new...

Signature cannot be verified error
Hi, We have a application which delivers SMIME mails to users. The user will upload his public key to our application and we will use our Entrust profile to sign and encrypt the mail. The application uses javamail api to do this. Recently one of our users has complained that he gets error while opening the mail.He uses entrust plugin with outlook in his machine to verify the signature. The user confirms that he has imported our root CA to his IE also. But when he opens his mail he gets signature verification failed error. The decryption happens properly and he can read the mail. Any idea why t...

Using Access 2003 Run-Time Version
I am trying to use the Access 2003 run-time and almost everything seems to be working just fine. I have noticed however that I seemed to have lost the ability to right click on a report and bring up the options of print/email.... When I right click using the run-time versionm nothing displays (as if the right button is disabled). When I right click using the regular mdb (full access 2003) all the options appear and I am able to print/email/etc at this time. My main question is is there any way to get the right click options back in a run-time version. Thanks That's correct: the run...

Budget date range
I need to set up my budget so that the month is the 15th throught the 14th, instead of the 1st throught the 31st. Can I do this in Money 2002 (OEM)? Sorry no! -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "Julie King" <anonymous@discussions.microsoft.com> wrote in message news:2cb1b01c393f5$dd4e9090$a601280a@phx.gbl... > I need to set up my budget so that the month is the 15th > throught the 14th, instead of the 1st throught the 31st. >...

Named Range question #2
I have several named ranges in my spread sheet. Basically I want to vary the name used in many formulas without having to manually modify those formulas. For example: There are many range names defined, say range_one, range_two, etc. There are many cells that use range names. For example Average(name_one), sum(name_one), etc. Again I want to change the range name in these formulas without manually modifying the formulas. Any way to do this? If there are not too many, you can do a Find and Replace and they will replace the names (even in the formulas). Don wrote: > I have severa...

9554 errors
I am getting the following event logged. It started a couple of days ago. Event Type: Warning Event Source: MSExchangeIS Event Category: General Event ID: 9554 Date: 21/04/06 Time: 2:04:21 PM User: N/A Computer: PEGASUS Description: Unable to update Mailbox SD in the DS. Mailbox Guid: af941007-94e5-47f5-8d34-e3fa85e3ac9c. Error Code 0x8004010f For more information, click http://www.microsoft.com/contentredirect.asp. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. I have followed KB articles 322308 and 555433 but have had no luck in finding o...

Query Builder Error 04-16-04
When adding a new lead and trying to save it I got this message : Query Builder Error No Atribute. I don't know if this has something to do with the new customisation that I did ( added few new fields and published them) or could it be a mapping issue ? Any help would be appreciatedn . I would republish all your objects and do an iisreset as a starting point jus to make sure everything is up to date "Yara" <anonymous@discussions.microsoft.com> wrote in message news:1ab7601c423bf$7e0a81c0$a601280a@phx.gbl... > When adding a new lead and trying to save it I got thi...

compiling error
I'm using Excel 2000 with Windows 98 second edition. Every time i start and/or exit Excel, there is this message: "compiling error in hidden module Auto Exec New" Has anyone come across the same problem? Please let me know. Thanx --- Message posted from http://www.ExcelForum.com/ On Thu, 5 Feb 2004 04:56:09 -0600, creimke <<creimke.115grj@excelforum-nospam.com>> wrote: >I'm using Excel 2000 with Windows 98 second edition. > >Every time i start and/or exit Excel, there is this message: > >"compiling error in hidden module Auto Exec New&...

Report error #2
I get the following error when running a prefilter report in CRM 3.0 but in VS in runs fine Reporting Services Error -------------------------------------------------------------------------------- a.. An error has occurred during report processing. (rsProcessingAborted) Get Online Help a.. Cannot set the command text for data set 'Kunde'. (rsErrorSettingCommandText) Get Online Help a.. Error during processing of the CommandText expression of dataset 'Kunde'. (rsQueryCommandTextProcessingError) Get Online Help Here is the sql statement SELECT accountnumb...

a range area referred anywhere and repeatively on a new worksheet page
Hi I have a grid / table which has prices on which i wish to do calculations on so i entered the information and then named it as a range pricegrid (pricegrid refers to cells between (a1:d26)) I then create a new worksheet which i wish to refer to pricegrid on the same page. if i am refer to =pricegrid on the next worksheet and the rows and columns exactly aligns with the value everything works i now want to move to a14 and want to use =pricegrid it will show value as it cant display in a different area i presume i can use the offset function but how can i create value on ...

Dynamic Range in a List Box with a blank choice?
Ok... so far so good !! 8-) ... I'm happy! I have a named dynamic range similar to this. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) .... and (by golly) it shows up in my list when I do Data>Validation>allow list on another sheet! But how do I provide a blank? What I want to do is if a referenced cell is blank (no input from the user) then the list shows a blank? A little bit about what I'm headed for: I am trying to develop a time sheet that will track: time spent running total time what task I spent the time on (this is where my list box comes in) then on another ...

Subtract time spent for lunch
I have a sheet for my time at work. I want to subtract time for lunch. Col B is "In" to work Col C is "Out" finished work. Everything is on 24 hr clock, no am or pm. I would like a col D "Lunch" in minutes or fractions of hour. B is 0800 arrive at work C is 1700 leave for the day. D is 0.50 for lunch E is 8.5 hours worked Again I want everything to be on 24 hour clock. I copied this formula from a template. =(C8-B8+(C8<B8))*24 The templates I found all had out/in for lunch. I just want one col with the time spent at lunch. M If you're going to use 0...

Date/Time Output Discrepancy
I've used two ways to get the {!Case.Created On} field but the outcomes were different. In 'E-mail To' action of Workflow Rule: the outcome was '07/21/2006 10:50 AM (GMT+08:00) Beijing, Chongqing, Hong Kong SAR, Urumqi' In the Email Template: the outcome was '07/21/2006 10:50 AM' How can I get a consistent format, and can it be presented in the other formats, e.g. UTC? Workflow by default will give you a fully formated date time string. To get the required result. you will have to create a small .net assembly that takes in the datatime parameter and retu...

e-mail errors
I used to have Outlook Express e-mail. I can no longer get it. It is now microsoft and that is not my default e-mail anymore. How can I get my e-mail now? "Marilyn" <mkeene@grande.com> wrote in message news:37EEFE75-68DD-4A63-8110-0415A7913C7B@microsoft.com... >I used to have Outlook Express e-mail. I can no longer get it. It is now >microsoft and that is not my default e-mail anymore. How can I get my >e-mail now? Windows Mail is the same as OE as far as setting it up. Windows Mail: Setting up an account from start to finish http://windo...

Syntax error when using names with apostrophe
i'm getting syntax error message for this button that tries to get name (of building) with apostrophe. Any idea how to solve this? Note that i must use name with apostrophe e.g. O'neal: Private Sub Command76_Click() On Error GoTo Err_Command76_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = ChrW(1506) & ChrW(1491) & ChrW(1499) & ChrW(1503) & ChrW(32) & ChrW(1508) & ChrW(1512) & ChrW(1496) & ChrW(1497) & ChrW(32) & ChrW(1502) & ChrW(1489) & ChrW(1504) & ChrW(1492) stLin...

send mail fail using cable modem
I cannot send mail using a cable modem. receive works fine. I installed outlook express and it can receive/send using same account as outlook, but outlook fails to send. do not get ans error message, email just disappears in a "black hole" -- outlook version 9.0.0.3821 -- using a linksys LNE100TX nic Surely it is because the email account (SMTP server) is incorrectly setup. Re-read your mail service provider's instructions. Hope this is useful to you. Let us know. rms curtis wrote: > I cannot send mail using a cable modem. receive works > fine. I install...