Runtime Error 1004 VBA

0
votes

I have some VBA code that gives me "Runtime Error 1004: PasteSpecial method of Range class failed."

This is my code:

Sub CurrentToOld()
rownumber = ThisWorkbook.Sheets("As-Built Emergency").Range("A1", Worksheets("As-Built Emergency").Range("A2").End(xlDown)).Rows.Count
ThisWorkbook.Sheets("As-Built Emergency").Activate
ThisWorkbook.ActiveSheet.Rows("2:" & rownumber).Select
Selection.Cut
ThisWorkbook.Sheets("OLD AB EMERGENCY").Activate
ThisWorkbook.ActiveSheet.Range("A2").Select
ThisWorkbook.ActiveSheet.Range("A2").PasteSpecial Paste:=xlPasteSpecial, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

The error is in the 2nd to last line, and I can't seem to figure out why. I'm copying around 150 rows, and there are no merged cells within any of the copied ones.

I've tried removing the .Select statements and I change what I copied from Rows("2:" & rownumber) to Range("A1:D4") [something small which exists] but I still get the error.

EDIT: I put this line:

ThisWorkbook.ActiveSheet.Range("A2").Select

Because I wanted to test if it was a problem with the way I wrote the PasteSpecial code or if I actually needed to declare anything.

But the statement above works when I tested it in the debugger, so I'm at a standstill here.

3
excelvba
When you say The error is in the 2nd to last line, do you mean the select line or the pastespecial line? – MatthewD

3 Answers

0
votes

Try just activating that range.

ThisWorkbook.ActiveSheet.Range("A2").Activate

or try declaring the worksheet

Sub CurrentToOld()
Dim wb      As Workbook
Set WorkBk = ActiveWorkbook
rownumber = WorkBk.Sheets("As-Built Emergency").Range("A1", Worksheets("As-Built Emergency").Range("A2").End(xlDown)).Rows.Count
WorkBk.Sheets("As-Built Emergency").Activate
WorkBk.ActiveSheet.Rows("2:" & rownumber).Select
Selection.Cut
WorkBk.Sheets("OLD AB EMERGENCY").Activate
WorkBk.ActiveSheet.Range("A2").Select
WorkBk.ActiveSheet.Range("A2").PasteSpecial Paste:=xlPasteSpecial, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Or declare the sheet as well

Sub CurrentToOld()

Dim wb      As Workbook
Set WorkBk = ActiveWorkbook
Dim ws As Excel.Worksheet
Set ws = Application.ActiveSheet

rownumber = WorkBk.Sheets("As-Built Emergency").Range("A1", Worksheets("As-Built Emergency").Range("A2").End(xlDown)).Rows.Count
WorkBk.Sheets("As-Built Emergency").Activate
ws.Rows("2:" & rownumber).Select
Selection.Cut
WorkBk.Sheets("OLD AB EMERGENCY").Activate
ws.Range("A2").Select
ws.Range("A2").PasteSpecial Paste:=xlPasteSpecial, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
0
votes
Sub CurrentToOld()
Dim rownumber As Long
rownumber = ThisWorkbook.Sheets("As-Built Emergency").Range("A1", Worksheets("As-Built Emergency").Range("A2").End(xlDown).Address).Rows.Count
ThisWorkbook.Sheets("As-Built Emergency").Activate
ThisWorkbook.ActiveSheet.Rows("2:" & rownumber).Select
Selection.Cut
ThisWorkbook.Sheets("OLD AB EMERGENCY").Select
ThisWorkbook.Sheets("OLD AB EMERGENCY").Range("A1").Select
ActiveSheet.Paste
End Sub
0
votes

Apparently the problem was that the sheet I was posting to had data, and I needed to clear out the used cells first.