ActiveSheet.paste works once, and then fails.

[shawleigh17]

For whatever reason, when I try to run a piece of code that contains activeSheet.paste, it will work the first time, but then fail any other time that I try to run it. Then, if I wait a little while, or run other pieces of code first, and try again, it will work. Not sure if anyone knows what might be causing this?

Sub Undo_All_Changes()
    Dim filename As String
    Dim Last_Row As Integer
        
    filename = "Warehouse_Allocationt_Tool.xls"
    Workbooks(filename).Activate
    Sheets("Layout_Manager").Select
    Application.Goto Reference:="Layout_Manager"
    Selection.Copy
    
    Sheets("Analyze_Layout").Unprotect ("fritoIPM")
    Sheets("Analyze_Layout").Visible = True
    Sheets("Analyze_Layout").Select
    Range("B4").Select
    Application.Goto Reference:="Layout_Manager"
    ActiveSheet.Paste 'fails here.
    Call ccc
    
    Workbooks.Open ("D:\Personal\O&S_Report1.xls")
    Workbooks("O&S_Report1.xls").Activate
    Sheets("SKU_Change_Plan").Unprotect ("fritoIPM")
    Sheets("SKU_Change_Plan").Select
    Columns("B:B").Select
    Selection.ClearContents
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "New Location"
    Range("B2").Select
    
    Last_Row = ActiveSheet.UsedRange.Rows.Count
    Range("A2").Select
    
    For i = 1 To Last_Row
        ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.FormulaR1C1
        ActiveCell.Offset(1, 0).Select
    Next i
    
    Application.DisplayAlerts = False
    Workbooks("O&S_Report1.xls").Activate
    ActiveWorkbook.SaveAs ("D:\Personal\O&S_Report1.xls")
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
    Workbooks("Warehouse_Allocationt_Tool.xls").Activate
    Application.DisplayAlerts = False
    Sheets("Print_Layout").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Sheets.Add().Name = "Print_Layout"
    Application.DisplayAlerts = True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Starting Location"
    ActiveCell.Offset(0, 1).FormulaR1C1 = "Ending Location"
    Columns.EntireColumn("A:B").AutoFit
    
    Workbooks(filename).Activate
    Sheets("Analyze_Layout").Protect ("fritoIPM")
    Sheets("Analyze_Layout").Select
    Range("A1").Select
End Sub

 

[Colin Legg]

Hi,

I haven't tried running your code but unprotecting the Analyze_Layout sheet will clear the clipboard which means that there will be nothing to paste.

I'm not sure what that first portion of your code is trying to achieve.


Hope that helps...

 

[shawleigh17]

Thanks for your help! I got it working yesterday by moving that line of code, but I wasn't sure why it worked. Now I know!

-Shawna

 

[Norie]

Shawna

Why use AciveSheet.Paste in the first place?

Sub Undo_All_Changes()
Dim filename As String
        
    strFileName = "Warehouse_Allocationt_Tool.xls"
    Workbooks(filename).Range("Layout_Manager").Copy
    
    With Sheets("Analyze_Layout")
        .Unprotect ("fritoIPM")
        .Range("Layout_Manager").PasteSpecial
    End With
    
    ' Call ccc
    
    Set wbOpen = Workbooks.Open("D:\Personal\O&S_Report1.xls")
    
    With wbOpenW.Sheets("SKU_Change_Plan")
        .Unprotect ("fritoIPM")
        .Columns("B:B").ClearContents
        .Range("B1").Value = "New Location"
        .UsedRange.Value = .UsedRange.Value
    End With
    
    Workbooks("O&S_Report1.xls").SaveAs ("D:\Personal\O&S_Report1.xls")
    Workbooks("O&S_Report1.xls").Close
    
    With Workbooks("Warehouse_Allocationt_Tool.xls")
    
            .Sheets("Print_Layout").Delete
            
            With .Sheets.Add().Name = "Print_Layout"
                .Range("A1:B1").Value = Array("Starting Location", "Ending Location")
                .Columns.EntireColumn("A:B").AutoFit
            End With
            
    End With
    
    With Workbooks(filename).Sheets("Analyze_Layout")
        .Protect ("fritoIPM")
        appplication.Goto .Sheets("Analyze_Layout").Range("A1"), Scroll
    End With
    
End Sub

Note this code will compilr but I don't know if it'll do what you want and the reason for that is because the use of all the Activate/Select.

That just maked the original code hard to follow.