Solved

Why suddenly I get "Can't redo", "Can't undo" and I can't copy and paste anymore in my Excel 2007?

Posted on 2016-09-29
21
37 Views
Last Modified: 2016-10-31
This happened all of a sudden without I doing anything special. I can only cut and get that dotted line display, but as soon as I move the cursor to another cell the dotted line becomes solid again and I can't copy and paste.

At the same time as this happened with Excel something very peculiar happened with internet: the connection to my Gmail account and to Experts Exchange was completely interrupted whereas other websites worked fine. At first, it displayed No internet access in the status field in my Windows 7, then it displayed normal but still the connection to my Gmail account and my Experts Exchange account was completely down. (Other accounts like my Hotmail account worked though.)

I am in Thailand now, and I know they are gradually implementing their own version of The Great Firewall of China. Can it have anything to do with that?

Now about 10 minutes later I can access my Gmail account and my Experts Exchange account again.

I've lived in Thailand in the same place for about 2 years, but for about the past half year internet is behaving much differently than earlier, very unpredictable.

However, I need to get my Excel back to working condition again. What should I do so that I can copy and paste again, and undo and redo?
0
Comment
Question by:hermesalpha
21 Comments
 
LVL 11

Assisted Solution

by:Missus Miss_Sellaneus
Missus Miss_Sellaneus earned 50 total points
ID: 41822910
I've had problems copying and pasting or redoing when my available RAM was low. Closing and restarting Excel might take care of that, and if not, you can try rebooting.
0
 
LVL 5

Assisted Solution

by:D Patel
D Patel earned 50 total points
ID: 41822917
Try Upgrading the Office.
0
 

Author Comment

by:hermesalpha
ID: 41822919
Missus, you might be right, because when I tried now with a backup of this file from yesterday (when I didn't have this problem), then this problem was the same on this old backup file. I'll try rebooting.
0
 

Author Comment

by:hermesalpha
ID: 41822923
No, it didn't work to reboot, still the same issue. Besides, I have almost nothing else opened then this Excel.
0
 

Author Comment

by:hermesalpha
ID: 41822928
They talk on this forum something about removing all filters:

http://www.tomsguide.com/forum/244522-49-excel-copy-paste-issue-help
0
 

Author Comment

by:hermesalpha
ID: 41822931
I run the GetOpenClipboard Windows now from Tomsguide.com, and nothing is wrong with my clipboard. But how come I get this trouble with an old Excel from yesterday although yesterday I didn't have this problem?
0
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 400 total points
ID: 41822932
Try doing a repair on MS Office
0
 

Author Comment

by:hermesalpha
ID: 41822940
I remember now that I had this issue a while ago in the same workbook, but I got it solved somehow.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41822947
Is there any VBA code in that workbook?
0
 

Author Comment

by:hermesalpha
ID: 41822949
A lot of VBA, I think that's what caused this issue last time.

On this particular sheet, I have for example below code (which displays a comment box upon hovering with the cursor over a cell).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim str As String, newstr As String, myseg As Long, mybrk1 As Long, mybrk2 As Long, mybrk3 As Long
On Error Resume Next
str = Target.Value
If Len(str) < 30 Then GoTo short
myseg = Round(Len(str) / 4)
mybrk1 = InStr(myseg, str, " ", vbTextCompare)
mybrk2 = InStr(myseg * 2, str, " ", vbTextCompare)
mybrk3 = InStr(myseg * 3, str, " ", vbTextCompare)
str = Left(str, mybrk1) & vbCrLf & Mid(str, mybrk1, mybrk2 - mybrk1) & vbCrLf & _
Mid(str, mybrk2, mybrk3 - mybrk2) & vbCrLf & Mid(str, mybrk3, (Len(str) + 1) - mybrk3)
short:
Cells.ClearComments
If Target <> "" Then
   Target.AddComment str
   Target.Comment.Visible = True
   Target.Comment.Shape.TextFrame.AutoSize = True
End If
End Sub

I have another VBA that I use with the key combination CTRL + M to mark a cell with yellow background colour, and CTRL + N to unmark.

I'm not sure about this VBA though:

Sub CopyFormat()
    Worksheets("LOPP 1").Range("CD2567").Copy
    Worksheets("Reducerade system").Range("C23").PasteSpecial xlPasteFormats
End Sub
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Accepted Solution

by:
Roy_Cox earned 400 total points
ID: 41822955
One of your code modules might be stopping  Copy & Paste and Undo.

This code copies a range from one sheet to another then pastes as values

Sub CopyFormat()
    Worksheets("LOPP 1").Range("CD2567").Copy
    Worksheets("Reducerade system").Range("C23").PasteSpecial xlPasteFormats
End Sub

Open in new window

0
 

Author Comment

by:hermesalpha
ID: 41822972
This sub, does it only copy between "LOPP 1" and "Reducerade system", and only CD2567 and C23?
0
 

Author Comment

by:hermesalpha
ID: 41822973
I have this sub also that does something with cut and paste:

'Range values for saving tab A. area (a) is A6-W76 in tab A (to tab "Lopp X", where "X" is a
'number from 1 to 15 or is empty; area on tab B to save to is BG3071-CC3141).
'Retriving to tab A. area (a) is from BG3071-CC3141 in tab B (tab A area (a) is A6-W76).
'Saving tab A. area (b) is Y81-DR138 in tab A (to tab "Lopp X", where "X" is a number from 1 to 15
'is empty; area on tab B to save to is CE3146-FX3203).
'Retriving to tab A. area (b) from CE3146-FX3203 in tab B.

Sub Save1()
    Sheets("Viktad rankning").Select
    Range("$A$5:$W$76").Select
    Selection.Copy
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("$BG$3071").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub

Sub Restore1()
    Sheets("Viktad rankning").Select
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("$BG$3071:$CC$3142").Select
    Selection.Copy
    Sheets("Viktad rankning").Select
    Range("$A$5").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub

Sub Save2()
    Sheets("Viktad rankning").Select
    Range("$Y$81:$DR$138").Select
    Selection.Copy
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("$CE$3147").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub

Sub Restore2()
    Sheets("Viktad rankning").Select
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("CE3147:FX3204").Select
    Selection.Copy
    Sheets("Viktad rankning").Select
    Range("$Y$81").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub
0
 

Author Comment

by:hermesalpha
ID: 41822977
I had asked this question earlier here at EE, but can't locate the answer to it now:

Why can I only copy and paste some drop-down lists and not others in Excel 2007?
0
 

Author Comment

by:hermesalpha
ID: 41822979
By the way, this issue now happened after I cut and pasted some drop-down list on this sheet.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41822983
I need to see the workbook. Not of the above code should be switching off Undo and Copy & Paste. I'll check back later, I'm off to work now.
0
 

Author Comment

by:hermesalpha
ID: 41822990
I can't upload it here, contains too much sensitive information.

But why can't I find my question I asked here earlier?:

Why can I only copy and paste some drop-down lists and not others in Excel 2007?
0
 

Author Comment

by:hermesalpha
ID: 41823002
Here is another VBA:

Option Explicit
Dim KeepValue(1 To 8) As Variant

Private Sub Worksheet_Calculate()
    Dim i As Integer
    On Error GoTo ErrorOut
    Application.EnableEvents = False
    For i = 1 To 8
        If KeepValue(i) <> Cells(34, i + 15) And Cells(35, i + 15).HasFormula = True Then
            Cells(35, i + 15) = Cells(34, i + 15)
        End If
        KeepValue(i) = Cells(34, i + 15)
    Next i
ErrorOut:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Q35:X35")) Is Nothing And Target.Count = 1 Then
        On Error GoTo ErrorOut
        Application.EnableEvents = False
            If IsEmpty(Target) Then
                Target.FormulaR1C1 = "=IFERROR(R[-1]C,"""")"
            End If
ErrorOut:
        Application.EnableEvents = True
    End If
    Dim r As Long
Dim rng As Range
If Not Intersect(Target, Range("CL:CL")) Is Nothing Then
   r = Target.Row
   Set rng = Union(Range("CH" & r & ":CK" & r), Range("CM" & r & ":GC" & r))
   If Target = "Inaktivera kategorin" Then
      rng.Locked = True
      rng.Font.Color = RGB(217, 217, 217)
   Else
      rng.Font.ColorIndex = xlAutomatic
   End If
End If
If Not Intersect(Target, Range("CC2567:CC2581")) Is Nothing Then
   On Error GoTo ErrorOccurs
   Application.EnableEvents = False
   Call MatchAndCopyRankingstyp
   Application.EnableEvents = True
End If
ErrorOccurs:
Application.EnableEvents = True
End Sub
Sub MoveUP()
   MoveRowsOrColumns ("Up")
End Sub

Sub MoveDown()
   MoveRowsOrColumns ("Down")
End Sub

Sub MoveRight()
   MoveRowsOrColumns ("Right")
End Sub

Sub MoveLeft()
   MoveRowsOrColumns ("Left")
End Sub


And another:

Sub MoveRowsOrColumns(direction As String)
    Dim rOriginalSelection As Range

    Select Case direction
    Case "Up", "Down"
        Set rOriginalSelection = Selection.EntireRow
    Case "Left", "Right"
        Set rOriginalSelection = Selection.EntireColumn
    End Select

    With rOriginalSelection
        .Select
        .Cut
        Select Case direction
        Case "Up"
            If rOriginalSelection.Row = 1 Then
               Application.CutCopyMode = 0
               Exit Sub
            End If
            .Offset(-1, 0).Select
        Case "Down"
            .Offset(rOriginalSelection.Rows.Count + 1, 0).Select
        Case "Left"
            If rOriginalSelection.Column = 1 Then
               Application.CutCopyMode = 0
               Exit Sub
            End If
            .Offset(0, -1).Select
        Case "Right"
            .Offset(0, rOriginalSelection.Columns.Count + 1).Select
        End Select
    End With
    Selection.Insert
    rOriginalSelection.Select
    Application.CutCopyMode = 0
End Sub

And a third code:

'Range values for saving tab A. area (a) is A6-W76 in tab A (to tab "Lopp X", where "X" is a
'number from 1 to 15 or is empty; area on tab B to save to is BG3071-CC3141).
'Retriving to tab A. area (a) is from BG3071-CC3141 in tab B (tab A area (a) is A6-W76).
'Saving tab A. area (b) is Y81-DR138 in tab A (to tab "Lopp X", where "X" is a number from 1 to 15
'is empty; area on tab B to save to is CE3146-FX3203).
'Retriving to tab A. area (b) from CE3146-FX3203 in tab B.

Sub Save1()
    Sheets("Viktad rankning").Select
    Range("$A$5:$W$76").Select
    Selection.Copy
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("$BG$3071").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub

Sub Restore1()
    Sheets("Viktad rankning").Select
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("$BG$3071:$CC$3142").Select
    Selection.Copy
    Sheets("Viktad rankning").Select
    Range("$A$5").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub

Sub Save2()
    Sheets("Viktad rankning").Select
    Range("$Y$81:$DR$138").Select
    Selection.Copy
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("$CE$3147").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub

Sub Restore2()
    Sheets("Viktad rankning").Select
    Sheets("Lopp " + Trim(CStr(CInt(Range("$AF$83").Value)))).Select
    Range("CE3147:FX3204").Select
    Selection.Copy
    Sheets("Viktad rankning").Select
    Range("$Y$81").Select
    ActiveSheet.Paste
    Sheets("Viktad rankning").Select
    Application.CutCopyMode = False
End Sub
0
 

Author Comment

by:hermesalpha
ID: 41823019
It works partly: I marked a cell for copying on tab B so I got the dotted line around the cell, then opened tab A and right-clicked on a cell, chose "Paste special" and "Formats", and it worked: I got the conditional formatting copied into that cell on tab A.

I can only copy and paste from another worksheet into worksheet A, but I cannot copy and paste from a cell on worksheet A to another cell on the same worksheet A.

Redo and undo also works fine as long as I copy and paste from another worksheet into worksheet A.

Copy and paste and also redo and undo works normal as they should on the other worksheets, it is only this worksheet A I can not use copy and paste and redo and undo.

I noticed now that it doesn't work completely normal copying from another worksheet to worksheet A: The first time I do the procedure, the option "Paste special" is greyed out when I right-click the cell on worksheet A for pasting. Only the second time "Paste special" is selectable.

And when I just had written above, it actually worked already on the first trial to copy and paste from worksheet B to worksheet A.
0
 

Author Closing Comment

by:hermesalpha
ID: 41866667
Now it's back to normal again, so I didn't need to do anything. Must have been some key combination I pressed that triggered this to happen. Probably that VBA code for copying and pasting.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41867268
Pleased to help
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now