Free Microsoft Excel 2013 Quick Reference

Why copy but not paste?

I’m trying to copy 16 cells located in various places on sheet1.

B6:G6, I6
B9:H9, J9

Paste them to the last row on sheet2.


Can this be done. If not why?

Post your answer or comment

comments powered by Disqus

I have a simple code to paste some data which is in "Sheet1" to "Sheet2".
Sub test1()
Worksheets("Sheet2").Paste Destination:=Range("a20")
End Sub

Although I am clearly defining that it should paste on "Sheet2" it
nevertheless pastes on "Sheet1". Any explanation why it does not paste on



Title pretty much says it all. I want a multiline textbox where the text can be selected and copied but not edited. Enabled = False obviously won't work because then you can't select. I realize I could do a change event macro that resets the textbox but I was hoping there was some "standard" way of doing it.

Thanks in advance

If I want to just copy row 3 to row 4, why does the following copy the data fine, but not paste and give error "Object does not support this property or method"?


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help.

Hello everyone
I have found the following code that I modified to fit my application. Problem is it will do everything but Paste the value that is copied. Any help is appreciated.
Also pretty new to VBA.

Sub test()
Dim searchThisRow As Range
Dim oneCell As Range
Dim itemsPostedCount As Long

Set searchThisRow = ThisWorkbook.Sheets("LogBook").Range("7:7")

For Each oneCell In searchThisRow
With oneCell
If oneCell = "Out of Service" Then

itemsPostedCount = itemsPostedCount + 1
.Offset(-1, 0).Select
MsgBox "Stop Here"

I wrote a somewhat simplistic procedure for duplicating menus (popup menus in my case) that relies on the CommandBarControl.Copy procedure. I installed a BeforeRightClick hook to gain control over whether the default popup or my copy is shown, so I can compare them in the exact same context. I used the List Range Popup menu for testing. It worked surprisingly well except for two issues.

1. Controls that have a toolbar "attached" to them, like Paste: instead of "Paste Options:" with a toolbar underneath, I get just "Paste" in the copy. What is the issue?

2. Occasionally a control is visible in the copy but not in the original and I can't figure out what is causing this. Case at point: the Paste Table control; it is grayed out but it is shown (for an empty table) whereas the original (also on an empty table) does not show it at all.

Any hints much appreciated.

im sorry if this has been answered before, i think it has but im having trouble explaining what im trying to do so searching for it online is really hard.

i have a xml feed which is updating a cell constantly, i would like to log that value in new cells over 8hrs.

so far my macro can copy the cell, paste the value into a new cell(lets say A2), then wait x seconds.

i can manually put in the next lot of code, which would be the same as the first but with the paste cell values changed, but i would like to repeat the code for a long time. ie i would like the copy cell(thats value is always changing) to stay on B1 but paste in a new cell every x seconds all the way down the column.

i am very new at this but i have found code to loop but i need to have the right code to loop.

any help would be appreciated, even if its the name of the process.



I switch off copy and paste through code when it is opened but I also replace the functionality before the sheet closes and saves. This has been working great for atleast 6 months but now it has decided not to work and has disabled paste. How can I get it paste back.

This is code in the ThisWorkbook section
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CutCopyMode = True
    Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.CellDragAndDrop = false
End Sub
What am I missing? If I put Code:
 in both sections,  I still can't paste.  I can copy but not paste on the sheet.  I haven't got any protection settings on.

Any help would be appreciated and thanks in advance


Here's my deal:

I inherited a workbook from a project that my boss worked on at her former company. She'd like to take that exact same workbook and use it for her new project because the same client liked the way it looked. Because the workbook is locked, I've been copying over every single formula by hand (can't even copy/paste from the formula bar), but not really understanding what they do because I'm not really an Excel data monkey. At least not this kind.

Anyway, I've learned much about how everything works in the workbook and every single formula has been copied over faithfully and works in the new book... except for one.

We have one data range (status) that contains information on where in the sales process a given unit is. We have another data range that contains the current selling price for the unit (asking_price). The formula in the cell in question is this:


On the old, locked workbook, there's a value. On my brand, spanking new workbook, I am getting a result of 0.

What does the "" part mean? If I could just find out what the "" part means, then I could perhaps find out why I'm getting a 0 because we're so early in the sales process for the new project that not all the variables are the same.

Background: An area containing data is manually hi-lited and designated for
copying into WorkSheet A (W/S-A). W/S-B is the intended destination.

Under the manual mode, the selected area is pasted into W/S-B without a hitch.

The paste process in W/S- B was carried out while a Macro was being recorded.

When the Macro was subsequently Run, it failed at Activesheet.Paste

Question: Why does the process work manually, but not from a Macro?


I thought I had it all set up now, but during testing I cut a chart and it
disapperard! I found that I CAN CUT out the chart! Not to delete, but cut...

Protection now is:
a/ In code I set:
Chart.ProtectData = True

b/ I protect the sheet with:
Sheet1.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
Sheet1..EnableSelection = xlUnlockedCells

Not so big purpose to protect the sourcecode of the charts and not allow to
delete them if the users can cut them, right? Can you please suggest a good
way of protect the charts of the sheet? You had to be able to copy paste
(to ppt or word etc) but not ruin the sourcecode in the charts or delete or
cut them out. (You know since I have code running on worksheet_change the
clipboard get empty and the chart is bye-bye...)

Background: I have a sheet with several charts. I protect them by in code
running a loop like:

For Each chtobj In Sheet1.ChartObjects
chtobj.Chart.ProtectData = True
Next chtobj

I also run other code, showing a splashscreen during the code to run then
opening the sheet and on change etc etc.

I started to write code to have the original charts on a veryhidden sheet
and just paste pictures in this worksheet, but it felt so large and I don't
know how to locate the 16 charts in the right spots in the sheet...
Hopefully you have some smart suggestion for me and I don't need to continue
with that route...

Kind regards

When I 'Copy' and 'Paste' an area from Excel and attempt to 'Paste Special' it as a 'Picture' (i.e. the best quality embedded item - I hope at least), into Word, I lose rows from the bottom.

It tends to happen on larger areas, so my thinking was that there may be a memory limit to the size of copy and paste areas ?????

Is this true ? Why do my Copy area not Paste the same into word ?

P.S. I end up Paste Special as a bitmap, but am not happy with the quality ?

In Excel 2007,

Why there is not option to Paste Special Values?

If I try to copy from Workbook to Workbook, the closest Paste Special option is Text.

Does anyone could clarify that?

Thanks in advance.

I have a textbox (activex) on a worksheet (Not a userform) and i want to copy the text to other cells.
The following code works just fine in excel 2010 but not in other excel programs like 2007.

De code is:

    SendKeys "^A^C", True ' send knoppen combinatie en CTRL A vanuit VBA
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works in excel 2010 but not in others?
Please help.


The following code (which I found on a forum and modifed) to email a single worksheet in Excel works fine if run in Excel 2002. However, if it is run from a co-workers system that is running Excel and Outlook 2003, an error message is received that says "Automation error; Specified module could not be found". Does anyone know why this would run in Excel and Outlook 2002 but not 2003? Thanks..

    Dim OL As Object 
    Dim EmailItem As Object 
    Dim FileName As String 
    Dim y As Long 
    Dim TempChar As String 
    Dim SaveName As String 
    Application.ScreenUpdating = False 
    Set OL = CreateObject("Outlook.Application") 
    Set EmailItem = OL.CreateItem(olMailItem) 
    FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name 
    For y = 1 To Len(FileName) 
        TempChar = Mid(FileName, y, 1) 
        Select Case TempChar 
        Case Is = "/", "", "*", "?", """", "", "|" 
        Case Else 
            SaveName = SaveName & TempChar 
        End Select 
    Next y 
    Selection.PasteSpecial Paste:=xlValues 
    Selection.PasteSpecial Paste:=xlFormats 
    ActiveWorkbook.SaveAs "C:" & SaveName 
    ActiveWorkbook.ChangeFileAccess xlReadOnly 
    With EmailItem 
        .Subject = ActiveWorkbook.Name 
        .To = "user@mail.Com" 
        .Importance = olImportanceNormal 'Or olImportanceHigh Or olImportanceLow
        .Attachments.Add "C:" & SaveName 
    End With 
    Kill "C:" & SaveName 
    ActiveWorkbook.Close False 
    Application.ScreenUpdating = True 
    Set OL = Nothing 
    Set EmailItem = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a bunch of rows and columns of numbers that I want to copy, but when I paste, I only want the values, not the formulas. How can I tell it not to copy the formulas, just the values.
Paste special isn't available when I do the copy.

Entering the same word will kick in AutoComplete sometimes but not others.
The word typed the same way exactly.

For example lets say I already have these 3 words ALREADY entered in 3
different cells but in the same column and they are the only words in my
spreadsheet beginning with the 2 letters Ne:

New Rochelle

If I begin to type in another cell of this same column (don't really think
which column should matter) like this:
Net (Autocomplete should now kick in by adding flix)

or if I type in:
new (Autocomplete should now kick in by adding Rochelle (space followed by

or if I type in:
neo (Autocomplete should now kick in by adding phyte).

and so on.

Well, don't know why but sometimes Autocomplete does this correctly but
other times does nothing.

What the heck is going on ?

Copying a word, pasting it, then deleting it, then starting to type in the
same word will sometimes get it to work,
BUT ONLY FOR ONE ENTRY - What the hell ?

I have developed a code that sends emails to the users listed in a MS excel coloumn. As the content of the emails contains rich text items I have kept the templates in separate word documents. While executing, the code opens the word documents based on selection, copies the contents and pastes it in the Lotus Notes body. Now the problem is that most of the times the code is unable to paste the data in the lotus notes body but when I do a (Ctrl + V) manually the data is getting pasted which means it is there in the memory. But don't know why it is not getting pasted on the Lotus Notes Body. As a result blank emails are getting delivered. Any suggestions or assistance will be highly appritiated.

    Set ws =
    Set s = CreateObject("Notes.Notessession")

    Set notesDb = s.GetDatabase("*******", "*********")
    If Not notesDb.IsOpen Then
        Call notesDb.Open("", "")
    End If

'Avoid error 429, when Word isn't open.

On Error Resume Next
'Set appWord object variable to running instance of Word.
Set wrdApp = GetObject("Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set wrdApp = CreateObject("Word.Application")
End If

Filename1 = ThisWorkbook.Path & "TokenDisable.doc"

Set wdDoc = wrdApp.Documents.Open(Filename1, , True)

    count = 5
    Do While Sheets("Main").Range("B" & count).Value <> ""

   If Sheets("Main").Range("M" & count).Value <> "" Then
        Set doc = notesDb.createDocument()
        doc.Form = "memo"
        doc.AltFrom = "LN"
        Call doc.ReplaceItemValue("$AltFrom", "LN")
        Call doc.ReplaceItemValue("$AltReplyTo", "LN")
        doc.From = "LN"
        Call doc.ReplaceItemValue("Sender", (Sheets("Main").Range("M2").Value))
        doc.DisplayFrom = "LN"
        doc.DisplayFrom_Preview = "LN"
        doc.DisplaySent = CStr(Sheets("Main").Range("M1").Value)
        doc.Subject = sub1
        doc.Recipients = CStr(Sheets("Main").Range("B" & count).Offset(0, 11).Value)
        'doc.ReplyTo = "LN"
        doc.Principal = "LN"
        'doc.ReturnReceipt = "1"
        'doc.Importance = "1"
        doc.SendTo = CStr(Sheets("Main").Range("B" & count).Offset(0, 11).Value)
' Populating Word Document
With wdDoc
.FormFields("fldTokenSerial").result = Sheets("Main").Range("N" & count).Value
.FormFields("fldanalyst").result = Sheets("Main").Range("M1").Value
.Visible = True
End With
        Set doc = ws.EDITDOCUMENT(True, doc) ##**## The Lotus Notes memo is getting edited
        Call doc.GOTOFIELD("Body")           ##**## The cursor is moving to the body     
        Call doc.FIELDAPPENDTEXT("Body")
        Call doc.Paste			     ##**## HERE Lies the issue, the data is not getting pasted. 	
        doc.SAVEMESSAGEONSEND = True
        doc.PostedDate = Now()
        Call doc.Send(False, CStr(Sheets("Main").Range("B" & count).Offset(0, 11).Value))
        Call doc.Save(True, True)
        Call doc.Close(True)
         Set doc = Nothing
        Sleep 1000
        count = count + 1
    wdDoc.Close (0)
    wrdApp.Quit (0)
    Set s = Nothing
    Set ws = Nothing
    Set notesDb = Nothing
    Set wrdApp = Nothing
    Set wdDoc = Nothing
    Application.DisplayAlerts = True
    VarAns = MsgBox("All emails have been successfully sent", vbOKOnly + vbInformation)


I have this bit of code which does multiple things, it opens a two files and pastes data from one to other and then adds th values to a sheet in that workbook. It then should loop through a folder and copy the data range A2:M14 and paste it into the template workbook on the identified sheet in the cell B2 and then close the file and open the next and paste the data range under the data there but also leaving a white line between them.

However currently it wont loop as it pulls an error at the highlighted line below and also does not copy and paste the correct data.

Option Explicit

Sub AverageGraph()
Dim i As String
Dim l As String
Dim wbCsv As Workbook
Dim wsMyCsvSheet As Worksheet
Dim lNextrow As Long

i = Range("B7").Value
l = Range("B8").Value

Application.Workbooks.Open "C:Documents and SettingsSeymourJDesktopBook1Template.xlsx"
Application.Workbooks.Open "C:Documents and SettingsSeymourJDesktopActual_Participation_02_2011.xls"



Range("B6").Value = i
Range("B7").Value = l

strFldr = "C:Documents and SettingsSeymourJMy DocumentsTasks"

strFile = Dir(strFldr & "Graphing_MTH_Actual_Curr_Year" & "_*.csv")

Application.Calculation = xlCalculationManual

lNextrow = 1

    If Len(strFile) > 0 Then
            Set wbCsv = Workbooks.Open(Filename:=strFldr & "" & strFile)
            Set wsMyCsvSheet = wbCsv.Sheets(1)
            With Workbooks("Book1Template.xlsx").Sheets("MTH")
                .Cells(lNextrow, 2) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A2:M14"))
            End With
            lNextrow = lNextrow + 1
        'close it
        'go to next file
            strFile = Dir
            Application.StatusBar = strFile
        Loop Until Len(strFile) = 0
    End If
End Sub
Can anyone work out why it is not running?



I am unable to drag the formula to the entire column, I can drag it to the row but not to the column and this is the case for any formula. Even Copy and Paste does not work. Any ideas why?


Hi everyone,

My macro skills have improved quite a bit since joining this site but I'm currently running into a problem that's driving me crazy. It's frustrating because the task is so simple and yet my code simply isn't working!

Here's what I'm trying to do:

1. Open Workbook2
2. Copy a range Workbook2
3. Paste range in original workbook

When I run the following code, nothing gets selected and nothing is copied. It's very strange because I've programmed similar tasks in VBA and never had a problem until now. Why does this not work??

Workbooks.Open Filename:="C:UsersdasoftwareDocumentsIHDA filesComplete" & FilePath

This is probably very easy to do but i have been unable to find the solution...

I would like to copy an entire worksheet to another excel file - i want the formulas to be copied but not to reference the sheet they were copied from.

At the moment i get this when i paste,

=VLOOKUP("Totals and Overall Averages:",'[Master Copy New.xls]Google Traffic UK'!A7:Q5000,5,FALSE)

I need it to copy like below,

=VLOOKUP("Totals and Overall Averages:",'Google Traffic UK'!A7:Q5000,5,FALSE)

It would save me so much time - id appreciate any help.



I'm using Excel to map automotive parts to vehicle applications (two different spreadsheets) for importation into a relational Access database. Production dates for parts rarely match the on-sale dates of the cars, so I've set up a macro that:

1. copies the start production date for a part record into the vehicle workbook at the top of the 'discontinued' column,
2. copies the end production date for the part into the vehicle workbook at the top of the 'release' column,
3. uses these pasted data points as criteria for the autofilter in the vehicle workbook as 'vehicle release date' < 'part end production date' and 'vehicle discontinued date' > 'part start production date'.

I find that the macro works perfectly in the autofilter for the vehicle discontinued date, but not for filtering the vehicle release date. The date is copied and pasted OK. When I check the custom filter dialogue box, the date has been entered in with the 'is less than' menu item selected, but it doesn't bring up any records unless you click on the 'OK' button once the dialogue box is open. I want to run the macro without having to run the autofilter manually at all. To troubleshoot the issue, I broke up the macro into two separate macros, one for release and the other for discontinued. Problem remains, even though the only difference between the two macros is the relative cell addresses. I've checked formatting of the cells for text vs. numeric and that doesn't appear to be a problem.

It's a complex problem to explain, but I'm hoping someone can help. the macro was set up through a combination of key-stroke recording and some code from other Excel users attempting similar ends.



I run a macro that keeps one primary file open and opens secondary files to copy data from and pastes to the main file.

m = Cells(65536, 1).End(xlUp).Row 
Selection.Insert Shift:=xlToRight 
Selection.Insert Shift:=xlToRight 
Cells(1, 4).Select 
Range("A4:A" & m & "").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've tried it several times and even did a "Step Into" and it will not paste the recently copied range into it. Am I doing something wrong? This same macro used to work recently, but no longer does and changes were not made to it. Is there something that prevents Excel from recognizing the active window? Any help would be greatly appreciated.

Any idea why the following code would error out in line 2 on one workbook but not another? I copied the one line, then the two and then the entire code from one workbook to another. It works in one, but not in the other. (Error 1004)

Set FirstBlank = Range("A5").End(xlDown).Offset(1, 0) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you,

No luck finding an answer? You could always try Google.