button gives run time error 1004

[daniels012]

I copy data from Outlook.

I have a button on a spreadsheet that pastes the data from outlook into the proper cells on the spreadsheet.

The Problem!!! If the user forgets to copy the data from outlook it gives a Runtime Error 1004. Can I put in my code something to give a message to the user to "copy the data from Oulook. vbcrtl then hit the button again." and take the user back to cell B2 on the spreadsheet. Not sure where to put the message as well?
Below is the code:

Sub PasteContactInfo()
'
' PasteContactInfo Macro
' Copies Data from Outlook
'

'
    Application.ScreenUpdating = False
    ActiveWindow.SmallScroll Down:=48
    Range("B61").Select
    ActiveSheet.Paste
    Range("B62").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=51
    Range("B63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-54
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=51
    Range("C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-48
    Range("D10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=45
    Range("D63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=48
    Range("E63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D7").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=48
    Range("F63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D8").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=48
    Range("G63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-45
    Range("D9").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=45
    Range("H63").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-51
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-9
    Application.ScreenUpdating = True
    Range("B1").Select
End Sub

Thank You,
Michael[/code]

 

[daniels012]

Exit Sub or End Sub??

Thank You though!!!!!!!

 

[Seti]

Exit Sub

This will cause the macro to terminate once it reaches the normal end of the code. The error handling code goes after this, then an End Sub.

Otherwise, once normal operations happen, if you don't have the Exit Sub, the code will run right into the error handling code, even when there is no error.

 

[daniels012]

I put the Exit Sub in there. It still executes the message box. Do you think there may be another ERROR somewhere? Is there a way to use On Error '1004' ???

What it does is:
Runs the code gives the MsgBox and then enters the data properly???
Go figure??


Michael

 

[Seti]

To see what error you are getting, change

MsgBox "You must copy data from Outlook before you can paste with this button."

to

MsgBox "You must copy data from Outlook before you can paste with this button. " & err.number

The error number will be appended to the end of your message and appear in the message box so don't click OK until you check the error number.

In the C&P from the help file, there is sample code using a Select Case clause to trap for specific error numbers. Maybe this is what you need. But really you need to find out what error is going on for sure first.

 

[daniels012]

That is soooo cool. It gave Error 438?? What is that?

Michael

 

[daniels012]

I went and put an apostrophe in front of all code so I could debug and find the error. I did find it!!
the line:

Range("D4").Select
    Selection.Erase

I guess selection.Erase is not working. I did clear but it took all the lines off of my page as well. I just want it to delete the text in the cell.

Michael

ps. I am learning soooooo much. ray:

 

[Seti]

Try Range("D4").ClearContents

Once it is working, you can post the whole code to see where it might be cleaned up a bit. For example, you don't need to select cells in most cases, the page ups and page downs (don't recall the exact wording) probably don't need to be there, etc.

 

[daniels012]

Here is the final code!!!

thank you for all your help!
Michael

' PasteContactInfo Macro
' Copies Data from Outlook
'

'
    Application.ScreenUpdating = False
    On Error GoTo CopyFirst
    ActiveWindow.SmallScroll Down:=48
    Range("B61").Select
    ActiveSheet.Paste
    Range("B62").Copy
    Range("D3").PasteSpecial Paste:=xlValues
    Range("B63").Copy
    Range("D5").PasteSpecial Paste:=xlValues
    Range("C63").Copy
    Range("D10").PasteSpecial Paste:=xlValues
    Range("D63").Copy
    Range("D6").PasteSpecial Paste:=xlValues
    Range("E63").Copy
    Range("D7").PasteSpecial Paste:=xlValues
    Range("F63").Copy
    Range("D8").PasteSpecial Paste:=xlValues
    Range("G63").Copy
    Range("D9").PasteSpecial Paste:=xlValues
    Range("H63").Copy
    Range("D2").PasteSpecial Paste:=xlValues
    Range("D4").Select
    Selection.ClearContents
    Range("B61:K63").Select
    Selection.ClearContents
    Application.ScreenUpdating = True
    Range("B1").Activate
    Exit Sub
CopyFirst:
    Range("B61:K63").Select
    Selection.ClearContents
    MsgBox "You must copy data from Outlook before you can paste with this button."
    Application.ScreenUpdating = True
    Range("B1").Activate
End Sub