0

I have essentially no experience with VBA. Just trying to copy data from one table and insert into a second table without copying over any data. It is copying the data and deleting the cells but it can't find the table to paste it in. Any assistance would be appreciated.

 Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim Table2 As Range

Set Sheet1 = Worksheets("Sheet1")
Set Sheet2 = Worksheets("Sheet2")
Set Table2 = Range("a2:c10")

a = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If Sheet1.Cells(i, 2).Value = 1001 Then

Sheet1.Rows(i).Cut
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Paste

End If
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
VBANovice1025
  • 3
  • 2
  • 2
    Not sure why you are doing a `Cut` and then a `Delete` as the delete will negate the cut, but the fact that you are looping forwards instead of backwards thru the rows is going to cause all kinds of problems. It's going to skip rows. When deleting rows, you have to step backwards. `For i = a to 2 Step -1` – braX Jan 08 '20 at 02:23
  • 2
    I'm pretty sure if you `Cut` a range, then `Delete` it, you can't access it anymore. Also, I very much suggest reading through [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BruceWayne Jan 08 '20 at 02:23
  • Thanks Guys, I have tried again and used Dim/Set however still encountering the same error. – VBANovice1025 Jan 08 '20 at 04:56
  • Are you getting the error on the paste line? You didnt mention where the error is occurring, but that's my guess, since you are not doing that right either - get the last row first, and then the paste later - read this: https://stackoverflow.com/questions/13635089/how-to-paste-in-last-row-of-column-b-in-excel/13635230 – braX Jan 08 '20 at 05:57

1 Answers1

0

The error message comes simply from the fact that a Range has no Paste-method. It has a PasteSpecial-method, you could use that, but there is a much simpler way to copy data from one range to another: When issuing the Cut (or Copy) method, give the destination range as 2nd parameter.

Now, the VBA Cut-method clears the content of the source-range, it doesn't remove the cells. If you want to delete the lines, you have to issue a Delete-statement. And if it comes to deleting something from a list, always loop backwards (see for example VBA isn't deleting blank cells).

Your code could look like this:

Dim rowCount as long, row as long
rowCount = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For row = rowCount To 2 step -1
    If Sheet1.Cells(row , 2).Value = 1001 Then
        Sheet1.Rows(row ).Cut Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        ' If you want to Delete the row in Sheet 1:
        Sheet1.Rows(row).Delete 
    End If
Next
FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Thanks! The code works when cut is replaced by copy. But it doesn't matter as the cells are getting deleted afterwards anyways. I have a table in sheet2 and it is pasting the values below the table. Do you know of a way to get the macro to paste in the table? – VBANovice1025 Jan 09 '20 at 01:08
  • You access tables in VBA with the `ListObjects`-property of a worksheet. Have a look at https://stackoverflow.com/questions/12297738/add-new-row-to-excel-table-vba how to add a row into a table. – FunThomas Jan 10 '20 at 07:55