Paste/Paste Special not available after changing worksheets

NashvillePat

New Member
Joined
Mar 6, 2007
Messages
11
This problem just started recently, so I wonder if is the result of an auto update done to Excel 2003.

When I copy one or more cells in one worksheet, then change worksheets the paste functions are not available. This is true no matter which method I try to use - right click, toolbar, or menu. It also happens when I copy from one workbook, open another, and change pages in the new workbook. If I copy from one workbook, open another workbook and don't change pages or selections it works just as it always has done. I can copy and paste within the same worksheet with no problems.

I can use the clipboard to perform the copy and paste, but the problem with that is that it pastes the value of a formula, not the formula.

I have noticed a similar problem in workbooks where my boss put some VBA code in to highlight the first and top cells of the column and row you are in. In that case, when you go back to the copied cell it is no longer in copy status. In these workbooks I can still copy within the same sheet with no problem.

Thank you in advance for any help that anyone can provide. I am an intermediate user and don't know much VBA, so please be patient if the answer is complicated and I have to ask more questions. :(
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If a macro runs after you copy, Excel may clear the clipboard. (If the copy is part of the macro, you should be OK, but if it's a different macro you could have trouble, depending on what the macro does).

Check to see that the macro is not running after you copy: if it is, stop it from running and see if that solves the problem.

Denis
 

NashvillePat

New Member
Joined
Mar 6, 2007
Messages
11
Thank you for your suggestion.

However, I can not find any macros that might be running in either of the two sheets where I have the most trouble with this. One of them is emailed to me as an attachment (I copy from here) and the other (where I want to paste) is one that I created and did not put any macros into it.

I poked my nose in the VBA for these sheets and do not see anything obvious. Remember, though, that I am a novice at VBA.
 

NashvillePat

New Member
Joined
Mar 6, 2007
Messages
11
No, no edits. I simply highlight the cells in worksheet one, right click, select "copy", and then switch to the sheet where I want to paste them. If the correct sheet is active I have no problems. If I have to select the worksheet paste and paste special becomes unavailable.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Strange. I wonder if a recalc event is causing the problem?

Alternatively, I have seen similar behaviour when pasting from a Citrix-based Excel session (eg, on TM1 or Cognos) into a standard Excel workbook. Is that the case here?

Denis
 

NashvillePat

New Member
Joined
Mar 6, 2007
Messages
11
No, I don't think so. I'm not familiar with Citrix, but my OS is Windows XP Pro and I using Excel 2003.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
How about a Worksheet-level event? Right-click the tab for the sheet, View Code, and see if there is any code that may trigger when you activate a sheet. Also look for code in the This Workbook object.

Denis
 

NashvillePat

New Member
Joined
Mar 6, 2007
Messages
11
For the particular sheet that I want to paste into there is a "private sub" that directs the document to a particular printer. There is nothing in the This Workbook object. I did find two things that may be effecting the paste/paste special command. This code is in a module:

Sub OIS_Email()
'
' OIS_Email Macro
' Macro recorded 07/09/2006 by PublicWorks2
'

'
Application.WindowState = xlMinimized
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
End Sub

I think this one must have been a macro that I was experimenting with and gave up on.

On two sheets in the workbook the following code appears. It makes the first cell(s) in the row and column you are in to be highlighted as an aid to being sure you are in the right column and row. My boss put this in. Now this code is known to disable the paste/paste special functions within the same sheet. Perhaps it is effecting other sheets in the workbook as well?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False



Columns(1).Interior.ColorIndex = 0

Columns(2).Interior.ColorIndex = 0

Rows(1).Interior.ColorIndex = 0



With Cells(Target.Row, 1).Interior

.ColorIndex = 6

.Pattern = xlSolid

End With



With Cells(Target.Row, 2).Interior

.ColorIndex = 6

.Pattern = xlSolid

End With



With Cells(Target.Column).Interior

.ColorIndex = 6

.Pattern = xlSolid

End With



Application.EnableEvents = True

End Sub



Thank you for all your help on this issue. :cool:
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top