Button doesn't work second time around

[KateNW]

Hi,

You guys were so helpful last time, I thought I'd try this again. So I have a form button that runs some macros and when I click it once, all is well and it does what I told it to. If I click it a second time I get the Run Time error 1004 PasteSpecial method of range class failed. So I press debug - literally do nothing, just close out of VB, then click the button again and it works - but same error the second time. How can this be?

Range("Rationale").Select
    Selection.Copy
    Workbooks("job evaluation Database for TP.xls").Activate
    Sheets("Paste Sheet Here").Visible = True
    Sheets("Paste Sheet Here").Select
    Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("B4:B8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Database").Unprotect
    Sheets("Database").Activate
    Range("A1").Activate
    ActiveCell.End(xlDown).Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("Paste Sheet Here").Select
    Range("E12:E44").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Database").Select
    Range("F1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("Paste Sheet Here").Select
    Range("F2:F9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Database").Select
    Range("AM1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("Paste Sheet Here").Select
    Range("A1:G44").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Selection.ClearFormats
    Range("A1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Database").Select
    Range("A1").Select
    Sheets("Database").Protect

I know the code is pretty basic, but I'm not very good at this. I'm really just trying to understand how it can work once, but give an error the second time, then not actually need fixing then work again!?
Thanks,
K

 

[Kaps_mr2]

I had a search on google and found a similar problem:-

http://www.ozgrid.com/forum/showthread.php?t=69150&page=1

the other person says that it was resolved by moving the unprotect statement to elsewhere in the code.

You might want to try and remove all the protect / unprotect (or at least comment them out) statements and see if that gets rid of the problem. Then if it does you know that it is the crux of the matter.

You can then put them back at different locations. I would put the unprotect further up. Thanks

Regards

Kaps

 

[Ruddles]

Something is (a) being initialised when you load the form the first time or (b) being run between form load and the point at which you click the button, that doesn't survive to the end of the code which handles the click event. By clicking End or Debug when an error occurs, you're forcing the form to initialise again and the error doesn't occur.

Can you show us the code which runs when the form is loaded? You've only showed us part of your code - we may need to see more of it. Exactly which statement does the error occur at?

 

[KateNW]

I tried moving the unprotect sheet code, with no change to the result, darn. Here is the code in its entirety

Sub Copy_Page()
'
' Copy_Page Macro
' Macro recorded 23/08/2010 by brennk03
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Range("Rationale").Select
    Selection.Copy
    Workbooks("job evaluation Database for TP.xls").Activate
    Sheets("Paste Sheet Here").Visible = True
    Sheets("Paste Sheet Here").Select
    Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("B4:B8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Database").Unprotect
    Sheets("Database").Activate
    Range("A1").Activate
    ActiveCell.End(xlDown).Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    [COLOR=red]Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _[/COLOR]
[COLOR=red]       xlNone, SkipBlanks:=False, Transpose:=True[/COLOR]
    Sheets("Paste Sheet Here").Select
    Range("E12:E44").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Database").Select
    Range("F1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("Paste Sheet Here").Select
    Range("F2:F9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Database").Select
    Range("AM1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("Paste Sheet Here").Select
    Range("A1:G44").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Selection.ClearFormats
    Range("A1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Database").Select
    Range("A1").Select
    Sheets("Database").Protect
 
End Sub

I've double checked and there are no other macros that I can find in either spreadsheet. What you're saying (Ruddles) makes sense, but I can't figure out what might be running. Oh, and the line that gets the error message is highlighted in red above.

Thanks!
K