Need Macro to paste from clipboard on open

[bradenkeith]

Hello all,

I am attempting to create a macro that will paste to cell E3 the latest value copied when opening the workbook depending on it's value. I need it to only paste the value if it is a 10 digit phone number. For example: "1231231234". It would be ideal if it were to remove dashes when the clipboard holds the following: "123-123-1234". Any help would be greatly appreciated. Thank you!

 

[JoeMo]

Hello all,

I am attempting to create a macro that will paste to cell E3 the latest value copied when opening the workbook depending on it's value. I need it to only paste the value if it is a 10 digit phone number. For example: "1231231234". It would be ideal if it were to remove dashes when the clipboard holds the following: "123-123-1234". Any help would be greatly appreciated. Thank you!

See if this does what you want. Note that if the clipboard contains a 10-digit number or "###?###?####", that value will be pasted to E3 on whatever sheet is active when the workbook opens. I have assumed that E3 does not contain a formula when the workbook opens.

This module goes into Thisworkbook - not a standard module.

Private Sub Workbook_Open()
Dim V As Variant
If Application.CutCopyMode <> False Then
    V = ActiveSheet.Range("E3").Value
    Application.ScreenUpdating = False
    With ActiveSheet.Range("E3")
        .PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        If .Value Like "###?###?####" Then
            If InStr(.Value, "-") = 4 Then .Replace "-", ""
        .NumberFormat = "#"
        .EntireColumn.AutoFit
        Else
            .Value = V
        End If
    End With
    Application.ScreenUpdating = True
End If
End Sub

 

[bradenkeith]

See if this does what you want. Note that if the clipboard contains a 10-digit number or "###?###?####", that value will be pasted to E3 on whatever sheet is active when the workbook opens. I have assumed that E3 does not contain a formula when the workbook opens.

This module goes into Thisworkbook - not a standard module.

Private Sub Workbook_Open()
Dim V As Variant
If Application.CutCopyMode <> False Then
    V = ActiveSheet.Range("E3").Value
    Application.ScreenUpdating = False
    With ActiveSheet.Range("E3")
        .PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        If .Value Like "###?###?####" Then
            If InStr(.Value, "-") = 4 Then .Replace "-", ""
        .NumberFormat = "#"
        .EntireColumn.AutoFit
        Else
            .Value = V
        End If
    End With
    Application.ScreenUpdating = True
End If
End Sub

Doesn't work for me.. Doesn't do anything. E3 is a blank cell, and I put it into thisworkbook. No go. Thanks for trying

 

[JoeMo]

Doesn't work for me.. Doesn't do anything. E3 is a blank cell, and I put it into thisworkbook. No go. Thanks for trying

It works for me. Possibly you installed it incorrectly. Remove it and install again like this:
To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code from your browser window and paste it into the white space in the VBE window.
4. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

The other possibility is that you have nothing on the clipboard when you open the file. Have you copied a cell in some other open workbook just before you open the workbook with the code in it?