0

I have tried to solve this seemingly easy problem, but can't find a solution. I'm trying to copy a selected cell, plus 2 cells that follow, then create a new row and paste the contents in the first cell of the new row.

The first part works fine, After selection the prompt asks if this is the correct cell to copy, then the copy happens, and a new row is created with the first cell selected, but the code fails on the PasteSpecial.

You will see comments of what I tried, in the code, that have not worked.

I'd appreciate any help that is given. Thanks in advance. Dave

Sub SelectCell_on_RowThenAdd2andAskYorN_AddRow_Paste()

Dim Msg, Style, Response, MyString
    Msg = "Is Job Number the job you wish to copy?"
    Style = vbYesNo
    Response = MsgBox(Msg, Style)
    
    If Response = vbYes Then   ' User chose Yes.
        MyString = "Yes"   ' Perform some action.

    ActiveCell.Select 'Takes active cell and offsets 1 to the right
    Selection.Resize(Selection.Rows.Count, _
        Selection.Columns.Count + 2).Select ' Resizes selection by 2 rows
        Selection.Copy
        
    Else   ' User chose No.
        MyString = "No"   ' Perform some action.
    
    MsgBox "Choose the correct Job Number," & (Chr(13)) & "then Click the Copy Button.", vbOKOnly
    
    End If
    
    Dim oNewRow As ListRow
    Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    oNewRow.Range.Cells(1, 1).Select
    'Cells(Selection.Row, 1).Select  --- Did not work
    'ActiveSheet.Paste  --- Did not work
    'Paste Special gives error PasteSpecial Method of Range Class Failed
    Selection.PasteSpecial Paste:=xlValues


End Sub
iidave
  • 1
  • You may want to start [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). That clean-up may help fix the issue. – BigBen Jan 27 '21 at 21:26
  • I'm such a nube, I thought my solution was simple. I'm not quite sure how to implement another variable. I tried Set rng = oNewRow.Range.Cells(1,1). Then Rng.PasteSpecial Paste:=xlValues. Errored out at Set rng... Scratches head. – iidave Jan 27 '21 at 23:21
  • What is the error message? – BigBen Jan 28 '21 at 01:42
  • Run-time error '438': Object doesn't support this property or method. – iidave Jan 28 '21 at 02:32
  • It seems like adding a new table row clears the clipboard. I would use value transfer (demonstrated [here](https://stackoverflow.com/questions/24294923/how-to-copy-only-values-in-excel-vba-from-a-range)) and elsewhere), to transfer the values to the new row, bypassing the clipboard. – BigBen Jan 28 '21 at 02:56
  • Thanks so much for your help. – iidave Jan 28 '21 at 22:41

0 Answers0