0

What I'm trying to setup, is for a user to be able to select a row of text from a table and click on a button to have Excel copy the selection to another worksheet and insert to a non-fixed address. Currently, I tried having it search for a specific text and insert the copied row after that text but I ran into Error 91 "Object variable or With block variable not set." If there is a better way to do this, I'm all ear.

Private Sub CommandButton1_Click()
    Selection.Copy
    Sheets("Form").Select
    Dim FoundRange As Range
    Dim RangeAddress As Range
    Set FoundRange = Sheet3.Cells.Find("SIGN-ON")
    RangeAddress = FoundRange.Address
    RangeAddress.Selection
    Selection.Insert shift:=xlDown
    Sheets("MasterList").Select
End Sub

EDIT: Fixed typo in the code.

Community
  • 1
  • 1
Tee
  • 3
  • 3
  • Is `SING-ON` in a specific column or anywhere on the worksheet? Do you have to insert a cell in order to place the selection there or can it just be in the next empty cell? Which worksheet gets the Paste (your code is confused on this point)? –  Sep 04 '15 at 02:32
  • It suppose to be "SIGN-ON", typo on my part, sorry. And, it can't just be paste on empty cell because of the formatting and content on that sheet. What I'm trying to have Excel to do, is copied a row from Sheet1 named "MasterList" to Sheet3 called "Form" EDIT: Forget to mention, "SIGN-ON" is in column A and any search can be limited to column A. – Tee Sep 04 '15 at 02:39

1 Answers1

0

You were trying to assign a Range.Address property (a string) into a Range object (e.g. RangeAddress = FoundRange.Address) and Selection is a range, not an action like .Select.

Private Sub CommandButton1_Click()
    Dim rngToCopy As Range
    Dim FoundRange As Range

    Set rngToCopy = Selection   'save the current selection so it won't be lost

    With Sheets("Form")
        On Error Resume Next
        Set FoundRange = .Cells.Find("SIGN-ON")
        On Error GoTo 0
        If Not FoundRange Is Nothing Then
            With FoundRange.Resize(rngToCopy.Rows.Count, rngToCopy.Columns.Count).Offset(1, 0)
                rngToCopy.Copy
                .Insert shift:=xlDown
                Application.CutCopyMode = False
            End With
        End If
    End With

End Sub

I've cut down on the operations by getting rid of relying on .Select. See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1