ActiveSheet.Paste error

I have a consolidation routine that will, if the user chooses, consolidate the output of all the
sheets in our generated report onto one sheet by appending them one after the other with the
ActiveSheet.Paste method. It has been working fine with no problems until one customer generated a
LOT of reports which means a lot of worksheets in the book and a lot of calls to cut and paste them
all onto one sheet. Through trial and error I found that my machine can handle 90 reports (90
worksheets pasted into one sheet via a loop) before the error occurs. The customer was generating 92
reports (but I do not know at what point the error occurred for him).

The runtime error is:
Method 'Paste' of object '_Worksheet' failed

On a few occasions the same .paste call generated this error first:
No more new fonts may be applied in this workbook.

I'm guessing it is related to available resources somehow but exactly what I have no idea.
Monitoring the system memory shows no increase as the program runs. After 50 or so iterations I do
notice a progressive slowdown beginning.

Here is the code that does the consolidation. The error occurs on the ActiveSheet.Paste call.

Robb
========================================================


Private Sub ConsolidateSheets(SourceWB As Workbook)
'CONSOLIDATES ALL REPORT SHEETS OF PASSED WORKBOOK ONTO ONE SHEET
Dim I As Integer
Dim R As Integer
Dim C As Integer
Dim CurrentRow As Integer
Dim MasterSheet As Worksheet
Dim FoundMasterSheet As Boolean

    FoundMasterSheet = False
    For I = 1 To SourceWB.Sheets.Count      'LOOP THROUGH ALL SHEETS OF PASSED WORKBOOK
        If Right(SourceWB.Sheets(I).Name, 8) = "{Report}" Then  'FIND FIRST REPORT SHEET
            If FoundMasterSheet = False Then
                FoundMasterSheet = True
                Set MasterSheet = SourceWB.Sheets(I)
                Set SPC_RS = SourceWB.Sheets(I)
                MasterSheet.PageSetup.Zoom = False
                MasterSheet.PageSetup.FitToPagesWide = 1
                MasterSheet.PageSetup.Zoom = 80
            Else        'FIRST REPORT SHEET ALREADY FOUND. NOW COPY TO IT
                CurrentRow = GetLastUsedRowCol(MasterSheet, "ROW") + 2  'GET ROW TO COPY TO
                R = GetLastUsedRowCol(SourceWB.Sheets(I), "ROW")        'GET RANGE OF CELLS TO COPY
                C = GetLastUsedRowCol(SourceWB.Sheets(I), "COL")
                SourceWB.Sheets(I).Activate    'SWITCH TO REPORT SHEET TO COPY FROM
                SourceWB.Sheets(I).Range(GetRangeString(1, 1, C, R)).Select 'SELECT DATA
                Selection.Copy                                          'COPY DATA
                MasterSheet.Activate        'SWITCH TO REPORT SHEET TO COPY TO
                MasterSheet.Cells(CurrentRow, 1).Select                 'SET LOCATION TO PASTE TO
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell  'SET A PAGE BREAK
                ActiveSheet.Paste                                       'PASTE DATA
            End If
        End If
    Next I

    ActiveSheet.PageSetup.FitToPagesWide = 1

    On Error Resume Next    'DELETE SHEETS THAT WERE COPIED
    Application.DisplayAlerts = False
    For I = SourceWB.Sheets.Count To 1 Step -1
        If Right(SourceWB.Sheets(I).Name, 8) = "{Report}" Then
            If SourceWB.Sheets(I).Name <> MasterSheet.Name Then
                SourceWB.Sheets(I).Delete
            End If
        End If
    Next I
    Application.DisplayAlerts = True
    On Error GoTo 0

    MasterSheet.Cells(1, 1).Select              'SELECT CELL A,1 (AESTHETIC REASONS ONLY)

End Sub


Public Function GetLastUsedRowCol(WS As Worksheet, RowOrCol As String) As Variant
'RETURNS THE NUMBER OF THE LAST ROW IN THE SPECIFIED WORKSHEET'S USEDRANGE
Dim CellWasEmpty As Boolean

    CellWasEmpty = (WS.Cells(1, 1) = "")         'CELL(1,1) IS EMPTY

    If CellWasEmpty Then                        'IF CELL IS EMPTY, THEN TEMP FILL IT
        WS.Cells(1, 1) = "."
    End If

    Select Case UCase(RowOrCol)
        Case "ROW": GetLastUsedRowCol = WS.UsedRange.Rows.Count
        Case "COL": GetLastUsedRowCol = WS.UsedRange.Columns.Count
        Case Else: MsgBox "Invalid value [" & RowOrCol & "] passed." & vbCrLf & _
                      "Module: modTarus" & vbCrLf & _
                      "Function: GetLastUsedRowCol", vbCritical, "Program Error"
                      GetLastUsedRowCol = 0
    End Select

    If CellWasEmpty Then WS.Cells(1, 1).Clear    'IF CELL WAS ORIGINALLY EMPTY, THEN EMPTY IT AGAIN

End Function


========================================================


0
8/24/2006 5:36:22 PM
excel 39879 articles. 2 followers. Follow

2 Replies
648 Views

Similar Articles

[PageSpeed] 34

Sometimes when you do things in excel, the cutcopymode flag is reset.

For instance, I copied a range, then added a page break.  As soon as I added
that page break, the marching ants disappeared from my copied range.

I'd try changing the order of these statements:

    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell  
    ActiveSheet.Paste 

So that you paste first, then add the page break.



Robb wrote:
> 
> I have a consolidation routine that will, if the user chooses, consolidate the output of all the
> sheets in our generated report onto one sheet by appending them one after the other with the
> ActiveSheet.Paste method. It has been working fine with no problems until one customer generated a
> LOT of reports which means a lot of worksheets in the book and a lot of calls to cut and paste them
> all onto one sheet. Through trial and error I found that my machine can handle 90 reports (90
> worksheets pasted into one sheet via a loop) before the error occurs. The customer was generating 92
> reports (but I do not know at what point the error occurred for him).
> 
> The runtime error is:
> Method 'Paste' of object '_Worksheet' failed
> 
> On a few occasions the same .paste call generated this error first:
> No more new fonts may be applied in this workbook.
> 
> I'm guessing it is related to available resources somehow but exactly what I have no idea.
> Monitoring the system memory shows no increase as the program runs. After 50 or so iterations I do
> notice a progressive slowdown beginning.
> 
> Here is the code that does the consolidation. The error occurs on the ActiveSheet.Paste call.
> 
> Robb
> ========================================================
> 
> Private Sub ConsolidateSheets(SourceWB As Workbook)
> 'CONSOLIDATES ALL REPORT SHEETS OF PASSED WORKBOOK ONTO ONE SHEET
> Dim I As Integer
> Dim R As Integer
> Dim C As Integer
> Dim CurrentRow As Integer
> Dim MasterSheet As Worksheet
> Dim FoundMasterSheet As Boolean
> 
>     FoundMasterSheet = False
>     For I = 1 To SourceWB.Sheets.Count      'LOOP THROUGH ALL SHEETS OF PASSED WORKBOOK
>         If Right(SourceWB.Sheets(I).Name, 8) = "{Report}" Then  'FIND FIRST REPORT SHEET
>             If FoundMasterSheet = False Then
>                 FoundMasterSheet = True
>                 Set MasterSheet = SourceWB.Sheets(I)
>                 Set SPC_RS = SourceWB.Sheets(I)
>                 MasterSheet.PageSetup.Zoom = False
>                 MasterSheet.PageSetup.FitToPagesWide = 1
>                 MasterSheet.PageSetup.Zoom = 80
>             Else        'FIRST REPORT SHEET ALREADY FOUND. NOW COPY TO IT
>                 CurrentRow = GetLastUsedRowCol(MasterSheet, "ROW") + 2  'GET ROW TO COPY TO
>                 R = GetLastUsedRowCol(SourceWB.Sheets(I), "ROW")        'GET RANGE OF CELLS TO COPY
>                 C = GetLastUsedRowCol(SourceWB.Sheets(I), "COL")
>                 SourceWB.Sheets(I).Activate    'SWITCH TO REPORT SHEET TO COPY FROM
>                 SourceWB.Sheets(I).Range(GetRangeString(1, 1, C, R)).Select 'SELECT DATA
>                 Selection.Copy                                          'COPY DATA
>                 MasterSheet.Activate        'SWITCH TO REPORT SHEET TO COPY TO
>                 MasterSheet.Cells(CurrentRow, 1).Select                 'SET LOCATION TO PASTE TO
>                 ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell  'SET A PAGE BREAK
>                 ActiveSheet.Paste                                       'PASTE DATA
>             End If
>         End If
>     Next I
> 
>     ActiveSheet.PageSetup.FitToPagesWide = 1
> 
>     On Error Resume Next    'DELETE SHEETS THAT WERE COPIED
>     Application.DisplayAlerts = False
>     For I = SourceWB.Sheets.Count To 1 Step -1
>         If Right(SourceWB.Sheets(I).Name, 8) = "{Report}" Then
>             If SourceWB.Sheets(I).Name <> MasterSheet.Name Then
>                 SourceWB.Sheets(I).Delete
>             End If
>         End If
>     Next I
>     Application.DisplayAlerts = True
>     On Error GoTo 0
> 
>     MasterSheet.Cells(1, 1).Select              'SELECT CELL A,1 (AESTHETIC REASONS ONLY)
> 
> End Sub
> 
> Public Function GetLastUsedRowCol(WS As Worksheet, RowOrCol As String) As Variant
> 'RETURNS THE NUMBER OF THE LAST ROW IN THE SPECIFIED WORKSHEET'S USEDRANGE
> Dim CellWasEmpty As Boolean
> 
>     CellWasEmpty = (WS.Cells(1, 1) = "")         'CELL(1,1) IS EMPTY
> 
>     If CellWasEmpty Then                        'IF CELL IS EMPTY, THEN TEMP FILL IT
>         WS.Cells(1, 1) = "."
>     End If
> 
>     Select Case UCase(RowOrCol)
>         Case "ROW": GetLastUsedRowCol = WS.UsedRange.Rows.Count
>         Case "COL": GetLastUsedRowCol = WS.UsedRange.Columns.Count
>         Case Else: MsgBox "Invalid value [" & RowOrCol & "] passed." & vbCrLf & _
>                       "Module: modTarus" & vbCrLf & _
>                       "Function: GetLastUsedRowCol", vbCritical, "Program Error"
>                       GetLastUsedRowCol = 0
>     End Select
> 
>     If CellWasEmpty Then WS.Cells(1, 1).Clear    'IF CELL WAS ORIGINALLY EMPTY, THEN EMPTY IT AGAIN
> 
> End Function
> 
> ========================================================

-- 

Dave Peterson
0
petersod (12004)
8/24/2006 6:37:07 PM
Dave, 

Thanks for the reply and the suggestion. Unfortunately it made no difference. It is still the 91st
iteration that breaks it every time. For grins I even removed the pagebreak line altogether. Still
broke on the 91st iteration. I also ruled out the possibility of data being the problem by changing
the loop to run backward through the sheets as well as trying different data. 

Robb


On Thu, 24 Aug 2006 13:37:07 -0500, Dave Peterson <petersod@verizonXSPAM.net> wrote:

>Sometimes when you do things in excel, the cutcopymode flag is reset.
>
>For instance, I copied a range, then added a page break.  As soon as I added
>that page break, the marching ants disappeared from my copied range.
>
>I'd try changing the order of these statements:
>
>    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell  
>    ActiveSheet.Paste 
>
>So that you paste first, then add the page break.
>
>
>
>Robb wrote:
>> 
>> I have a consolidation routine that will, if the user chooses, consolidate the output of all the
>> sheets in our generated report onto one sheet by appending them one after the other with the
>> ActiveSheet.Paste method. It has been working fine with no problems until one customer generated a
>> LOT of reports which means a lot of worksheets in the book and a lot of calls to cut and paste them
>> all onto one sheet. Through trial and error I found that my machine can handle 90 reports (90
>> worksheets pasted into one sheet via a loop) before the error occurs. The customer was generating 92
>> reports (but I do not know at what point the error occurred for him).
>> 
>> The runtime error is:
>> Method 'Paste' of object '_Worksheet' failed
>> 
>> On a few occasions the same .paste call generated this error first:
>> No more new fonts may be applied in this workbook.
>> 
>> I'm guessing it is related to available resources somehow but exactly what I have no idea.
>> Monitoring the system memory shows no increase as the program runs. After 50 or so iterations I do
>> notice a progressive slowdown beginning.
>> 
>> Here is the code that does the consolidation. The error occurs on the ActiveSheet.Paste call.
>> 
>> Robb
>> ========================================================
>> 
>> Private Sub ConsolidateSheets(SourceWB As Workbook)
>> 'CONSOLIDATES ALL REPORT SHEETS OF PASSED WORKBOOK ONTO ONE SHEET
>> Dim I As Integer
>> Dim R As Integer
>> Dim C As Integer
>> Dim CurrentRow As Integer
>> Dim MasterSheet As Worksheet
>> Dim FoundMasterSheet As Boolean
>> 
>>     FoundMasterSheet = False
>>     For I = 1 To SourceWB.Sheets.Count      'LOOP THROUGH ALL SHEETS OF PASSED WORKBOOK
>>         If Right(SourceWB.Sheets(I).Name, 8) = "{Report}" Then  'FIND FIRST REPORT SHEET
>>             If FoundMasterSheet = False Then
>>                 FoundMasterSheet = True
>>                 Set MasterSheet = SourceWB.Sheets(I)
>>                 Set SPC_RS = SourceWB.Sheets(I)
>>                 MasterSheet.PageSetup.Zoom = False
>>                 MasterSheet.PageSetup.FitToPagesWide = 1
>>                 MasterSheet.PageSetup.Zoom = 80
>>             Else        'FIRST REPORT SHEET ALREADY FOUND. NOW COPY TO IT
>>                 CurrentRow = GetLastUsedRowCol(MasterSheet, "ROW") + 2  'GET ROW TO COPY TO
>>                 R = GetLastUsedRowCol(SourceWB.Sheets(I), "ROW")        'GET RANGE OF CELLS TO COPY
>>                 C = GetLastUsedRowCol(SourceWB.Sheets(I), "COL")
>>                 SourceWB.Sheets(I).Activate    'SWITCH TO REPORT SHEET TO COPY FROM
>>                 SourceWB.Sheets(I).Range(GetRangeString(1, 1, C, R)).Select 'SELECT DATA
>>                 Selection.Copy                                          'COPY DATA
>>                 MasterSheet.Activate        'SWITCH TO REPORT SHEET TO COPY TO
>>                 MasterSheet.Cells(CurrentRow, 1).Select                 'SET LOCATION TO PASTE TO
>>                 ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell  'SET A PAGE BREAK
>>                 ActiveSheet.Paste                                       'PASTE DATA
>>             End If
>>         End If
>>     Next I
>> 
>>     ActiveSheet.PageSetup.FitToPagesWide = 1
>> 
>>     On Error Resume Next    'DELETE SHEETS THAT WERE COPIED
>>     Application.DisplayAlerts = False
>>     For I = SourceWB.Sheets.Count To 1 Step -1
>>         If Right(SourceWB.Sheets(I).Name, 8) = "{Report}" Then
>>             If SourceWB.Sheets(I).Name <> MasterSheet.Name Then
>>                 SourceWB.Sheets(I).Delete
>>             End If
>>         End If
>>     Next I
>>     Application.DisplayAlerts = True
>>     On Error GoTo 0
>> 
>>     MasterSheet.Cells(1, 1).Select              'SELECT CELL A,1 (AESTHETIC REASONS ONLY)
>> 
>> End Sub
>> 
>> Public Function GetLastUsedRowCol(WS As Worksheet, RowOrCol As String) As Variant
>> 'RETURNS THE NUMBER OF THE LAST ROW IN THE SPECIFIED WORKSHEET'S USEDRANGE
>> Dim CellWasEmpty As Boolean
>> 
>>     CellWasEmpty = (WS.Cells(1, 1) = "")         'CELL(1,1) IS EMPTY
>> 
>>     If CellWasEmpty Then                        'IF CELL IS EMPTY, THEN TEMP FILL IT
>>         WS.Cells(1, 1) = "."
>>     End If
>> 
>>     Select Case UCase(RowOrCol)
>>         Case "ROW": GetLastUsedRowCol = WS.UsedRange.Rows.Count
>>         Case "COL": GetLastUsedRowCol = WS.UsedRange.Columns.Count
>>         Case Else: MsgBox "Invalid value [" & RowOrCol & "] passed." & vbCrLf & _
>>                       "Module: modTarus" & vbCrLf & _
>>                       "Function: GetLastUsedRowCol", vbCritical, "Program Error"
>>                       GetLastUsedRowCol = 0
>>     End Select
>> 
>>     If CellWasEmpty Then WS.Cells(1, 1).Clear    'IF CELL WAS ORIGINALLY EMPTY, THEN EMPTY IT AGAIN
>> 
>> End Function
>> 
>> ========================================================

0
8/24/2006 6:57:21 PM
Reply:

Similar Artilces:

Open or Active Linked Object Error
I've been editing a PowerPoint presentation -- only 6 slides. When I try to save it, I get an error message that says ~ there is an open or active object or link that cannot be saved automatically. Close that object first. The problem is, I can't fina any "open or active links or objects." Any advice on how to locate and close these things? Thanks, David Edit Menu, "Links" should show you any linked objects. -- James Orlando (Goofy says "Hey"), Florida "Doctor L" <Doctor L@discussions.microsoft.com> wrote in message ...

MSExchangeTransport Error 348 - Message stuck in queue
For the past day or so we have had an email message stuck in our queue and we've seen the following message in our event logs: Log Type: Application Event Type: Error Event Source Name: MSExchangeTransport Event ID: 348 Event Description: A message could not be virus scanned - this operation will be retried later. Internet Message ID <s4cdc690.079@smtpout.law.duke.edu>, Error Code 0x0. We are running Symantec Mail Security for Exchange version 4.6.1.107. I found KB article 842801 (http://support.microsoft.com/default.aspx?scid=kb;en-us;842801) which talks about the error occu...

error on outgoing mail
Hi ; I can't send e-mail from my website e-mail accounts. I get this messages: "The connection to the server has failed. Account: "custserv@xxxxxxxxx.com", server:"mail.xxxxxxxxx.com', protocol:SMTP, Port: 25, Secure(SSL):No, Socket Error: 10060, error number: 0x800CCC0E" I have no problem sending e-mails from the same accounts. I'm a newbie !! somebody can help me ? Thanks ! ...

paste info from excel into another spreadsheet & have todays date.
i would like to transpfer data from an excel spreadsheet into antoher document, but when i paste it I would like the current date to automatically transfer next to it. hi, i am not sure that can be done with a simple paste command you can use the keyboard shortcut Ctrl + L to insert the date after the paste. >-----Original Message----- >i would like to transpfer data from an excel spreadsheet into antoher >document, but when i paste it I would like the current date to automatically >transfer next to it. >. > Hi not sure that Ctrl & L is the standard shortcut fo...

activesheet
Hi i have a workbook that has 53 worksheets 52 of the worksheets are name week numbers ie week 1 , week 2 , week 3..................week 52 i have on worksheet called graph each week data is entered onto the relevent week number worksheet when i click a button the data is transfered to the graph worksheet what i would like to do is after the data is pasted onto the graph worksheet return to the sheet i was working on ie if i on worksheet week 2 i click the button i want to return to week 2 thanks kevin Most times, you don't have to change sheets to work on them. So maybe your code cou...

GDI.exe error in Publisher with HP printer
Printing from Publisher 2002 to a HP 960c deskjet. I keep getting "Mspub has caused an error in GDI.exe". I reinstalled new print drives. Does anyone know how to fix it? What is wrong? I dont' have the BDDenver font installed either! What version Windows? This is a fix for Windows 98 WD2000: IPF in Gdi.exe When Opening, Printing, or Repaginating Large Document http://support.microsoft.com/default.aspx?scid=kb;EN-US;220460 It is not Publisher specific but it maybe helpful -- Mary Sauer MS MVP http://office.microsoft.com/clipart/ http://www.mvps.org/msauer/getting_started.htm ...

Error 36884 from source Schannel in System EventLog
On two servers in EventLog (System) are next new records: /------------------------------------8<----------------------------------\ Event Type: Error Event Source: Schannel Event Category: None Event ID: 36884 Date: 20.11.09 Time: 21:09:05 User: N/A Computer: CTX4 Description: The certificate received from the remote server does not contain the expected name. It is therefore not possible to determine whether we are connecting to the correct server. The server name we were expecting is HSDC01.nerv.cz. The SSL connection request has failed. The attached data contains th...

Sub error
I have created this macro but everytime I run it the Columns it hides is incorrect. It hides FGHIJK it should only hide GHI. Can anyone tell me why this is happening? Thank you for your help. Sub Done_Changes() ' ' Done_Changes Macro ' ' Range("G11:H50").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=42 Range("G54:H58").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=9 Range("G62:H74").Select Selection.Locked = T...

How to set up past due notices based on dates
How can I use a date function to setup a past notice when a date expires? A second question is how to set up a date function that gives a notice prior to the expiration date say 30 days prior? "Gives a notice" Please define what you mean by "give a notice". **************** Anne Troy www.piersontech.com www.mrexcel.com "Linn" <Linn@discussions.microsoft.com> wrote in message news:D7E1A9C2-152F-4591-82DB-6D6D80ED5C87@microsoft.com... > How can I use a date function to setup a past notice when a date expires? A > second question is how to set up a...

VB error
Hi, I have the code below that will create a new worksheet and rename it after todays date. However if the user trys to create a log again , ie for the same day, I get this error:- Run-time error 1004 cannot rename a sheet to the same name as another sheet, a referenced object or a workbook referenced by visual basic. Oh course what I want is a msg box advising the user that one already exsists ans asking if they wanna view it. the code I have Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=She...

Public Folder replication error
when I click on a public folder and goto properties in ESM I get this error The operation Failed because of an HTTP error 501 (Not Implemented) ID no: c1020af6 now I followed this http://support.microsoft.com/default.aspx?scid=kb;en- us;839744 and it does not fix the problem need some help so I Can remove my first exchange Server from the site ...

BITMAP Paste frame
Hi, After pasting a Bitmap picture into a window, I hope there is a rectangle surrounding the bitmap so that users can move it. I wonder how to create an active-edge rectangle just like the style in Microsoft Photo Editor(PHOTOED.EXE). William "William" wrote: > Hi, > > After pasting a Bitmap picture into a window, I hope there is a rectangle > surrounding the bitmap so that users can move it. I wonder how to create an > active-edge rectangle just like the style in Microsoft Photo > Editor(PHOTOED.EXE). > > William > > Try to use CRectTracker...

Copy and paste 01-14-10
Hi, I have a problem with copying and pastig. I have wrote a code wich copies a table after sorting it and pastes i in to a new workbok. Everything works fine when I'm standing in the table and runs the macro, but if I'm standing above the table it copies all the rows and not only the filtered ones. What have I done wrong? Do anyone knows? -- Best regards Mia Mia, > What have I done wrong? Do anyone knows? How can anyone possibly offer an informed solution unless you post your code? Mike -- When competing hypotheses are equal, adopt the hypothesi...

pasting formulae to entire column in macro
Apologies to all if this is really easy! I'm a fairly new user who ha been thrown in at the deep end. I am trying to create a macro in which I will add a new column, write formula in one cell and then copy that formula to every cell in tha column. The spreadsheet in which the macro runs varies in the number of lines All I seem to be able to do is paste the formula to all 65,000 cells o to a fixed amount of cells. Can anyone help please? Thanks Coli -- Message posted from http://www.ExcelForum.com Hi use something like the following Sub insert_formulas() Dim LastRow As Long LastRo...

Error at GL Account Inquiry
GP 10 user gets the following error when she tries to inquire on GL account: A get/change first operation on table 'GL_Period_TEMP' cannot find the table. The table exists. What would this be? 1) Does this happen just to her or anyone else having this problem? 2) Has she tried this inquiry from another computer? If so, did she get the same error message? 3) If she tries from another machine and does not get the error, then it means her ODBC settings are wrong on her machine 4) If she tries from another machine and does get the same error, then you should probably run...

copy/paste vaules, not formulas
Is there an easier way to copy and paste the values of a cell without using notepad? When i paste directly to new column the formulas get copied--i only want to paste the final values... Makes sense? Jeremy -- Message posted via http://www.officekb.com Hi jeremy Copy the cells Right click on the target cell Choose Paste Special Select Values OK -- Regards Ron de Bruin http://www.rondebruin.nl "jeremy via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:2af487a568964ca5af6a0c284eb172b0@OfficeKB.com... > Is there an easier way to copy and paste the val...

Error code 0X80070057 (outlook 2003
In POP mail, I am getting error 0x80070057. ISP said it is a MS error. Trying to retrieve messages for pop.mindspring.com. Any help appreciated. I do not have Bells & Whistles on my system. John Lenz <lenz4@mindspring.com> wrote: > In POP mail, I am getting error 0x80070057. ISP said it is a MS error. > Trying to retrieve messages for pop.mindspring.com. Any help > appreciated. I do not have Bells & Whistles on my system. Are you using these settings? <http://kb.earthlink.net/case.asp?s=st%3D530%2Ce%3D0000000001527614717%2Ck%3D2416%2Csxi%3D18&article=21027...

Broken Error (No On Error Functionality)
In MS Excel Subs, I cannot turn off error dialogs using 'On Error Resume Next' or 'On Error Goto label' etc. It was not always thus. Once, I happily used utility subs that with 'On Error Resume Next' lines, many from John Walkenbach's book. Then, suddenly, these macros suddenly rebelled, halting execution at the first error, and displaying the "index out of range error dialog window"...the one that lets you click a debug button to go to the code in the VBE. I have rewritten all macros to avoid any sort of error handling. But, it sure would be nice to han...

Cap gains report and porfolio error in foreign currency investment
Using Money 2004 standard edition (I understand the same applies to Money 2005): Tax-Capital gains report of an investment in foreign currency shows an error. I'd appreciate if MVP's can try the recreation of the error and advise what plans Microsoft Development team has regarding correction of the error. The error can be recreated as follows: 1: set up a file using CA$ as base currency and an exchange rate of US$1 =CA$1.2 2: set up an investment account in US$ 3: 1/Jan/05, buy 1 share of MSFT @ US$100 (exchange rate US$1=CA$1.5 4: 2/Jan/05, sell 1 share of MSFT @ US$100 (exc...

Number Format Changes During VBA Copy/Paste
I have an Excel workbook with VBA code that copies a series of numeric values from sheet 2 and pastes them into corresponding cells in sheet 1. In one case, the value in sheet 2 is 0.0170, but when pasted into sheet 1 it displays as 0.0200. Both cell formats appear to be identical, there's no rounding going on in the VBA, so can't for the life of me figure out why this is happening. Anyone have an idea? (Excel 2007 on Windows XP Pro) post your code... -- HTH... Jim Thomlinson "LarryP" wrote: > I have an Excel workbook with VBA code that copi...

File not found error opening Visual Basic Editor
Hi I have some VBA code in Outlook that automates processing of some email files. Some of these functions are even part of rules I have set. This has been running well for over a month but since yesterday I can't run nor access the macros any more. Whenever I try to open Outlook Visual Basic editor I get a File Not Found error. If I insist and try again, all I get is an empty editor with all options disabled. I have already repaired office and tryed renaming the VbaProject.OTM which seems to make the error go away when I try to open VBeditor but this way my code will also go away....

ActiveX error on HTML add-in
Hi, i created an HTML add-in which gets called from a custom POS button. Though everything work fine as desired i am getting "An activex control on this page might be unsafe to interact with other parts of the page. do you want to allow this interaction" warning every time i click on the custom pos button. if i say ok on the prompt it works fine. I have defined the object in the head tag of the html page as belows: <OBJECT classid="clsid:44C4C3AC-D0F1-11D2-919D-006008C88FC3" height=0 id=qsBridge style="LEFT: 0px; TOP: 0px" width=0> &...

Activesheet.Calculate
Can I add the above function to my mouse right-click list of commands? If so, how? Jim May Hi Jim you may use the following code (add this entry to the right-click cell menu): Sub Add_Item() Dim New_Entry As Object Set New_Entry = CommandBars("cell").Controls.Add(Temporary:=True) On Error Resume Next New_Entry.Controls("Calculate").Delete On Error GoTo 0 With New_Entry .Caption = "Calculate" .OnAction = "Calculate_sheet" End With End Sub Sub calculate_sheet() ActiveSheet.calculate End Sub Sub Delete_Item() Dim myContro...

Copy and Paste from Web Site
If there is a table of figures on a web site, is there a way to copy and paste into Excel so the figures stay in the same columns as on the site, and can be worked on as numbers within Excel. Paste Special sometimes works (by pasting as Text, Unicode Text or even HTML) and sometimes it doesn't (all the data gets pasted into column A and not spread across columns). I realize this might be a function of how the HTML is written on the web site, but is there some conversion tool out there which will yield consistent results? Tx for your help and suggestions. I've had similar prob...

Excel 2000+XP copy/paste in new sheet drops 4 years from date
I have a customer who is using Excel XP. He is copy and pasting a date with format 10/20/2003 from one sheet to another. When he pastes onto the second sheet it drops 4 years off the date. I tried this on my system also, I am running Excel 2000 and I get the same behavior. Is there some kind of date setup that I need to look at. I am thinking this may be somehow related to the 1900/1904 date issue, am I correct? One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click ...