How do I prevent pasting into multiple columns?

Viewed 724

I am quite new to VBA, and I have encountered an odd issue with the following code snippet. My goal is to insert rows when a user pastes data manually into a table. The user copies a portion of the table manually (let's say column A1 through C25 -- leaving cloumns D and E untouched), and when pasting it manually into A26, the rows are inserted. This way, the table expands in order to properly fit the data (because there is more content under the table).

Now, the code shown below does sorta work, the only issue I am having is that the pasted data in columns (A through C) are repeated on all columns (D through F, G through I, etc..)

How do I prevent this pasted data from overwriting my other columns on the rows that I inserted (and from continuing "forever")

' When cells are pasted, insert # of rows to paste in
Dim lastAction As String
' If a Paste action was the last event in the Undo list
lastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
If Left(lastAction, 5) = "Paste" Then
    ' Get the amount that was pasted (table didn't expand)
    numOfRows = Selection.Rows.Count
    ' Undo the paste, but keep what is in the clipboard
    Application.Undo
    ' Insert a row
    ActiveCell.offset(0).EntireRow.Insert
End If

The reason I am using the command bar's undo control is because this code needs to run on a manual paste event.

1 Answers
Related