0

I am writing a macro to copy some data from one sheet to another but I keep getting an error 1004 "application-defined or object-defined error" on Range("A1").End(xlDown).Offset(1, 0).Select and I cannot figure out why. The sheet isn't locked or protected and, as far as I can tell, the syntax is correct on everything. Any ideas on why I might be having this issue? Code below.

Sub TransferData()
    
    Sheets("Defect Input").Select
    ActiveSheet.Range("C1:C3").Cut
    Sheets("Defect Table").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    Sheets("Defect Input").Select
    Range("C5:C30").Cut
    Sheets("Defect Table").Select
    Range("D1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    Sheets("Defect Input").Select
    Range("C33:C34").Cut
    Sheets("Defect Table").Select
    Range("AC1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("Defect Input").Select
    
    Application.CutCopyMode = False
    
    Range("C1:C3").Select
    Selection.ClearContents
    
    Range("C5:C30").Select
    Selection.ClearContents
    
    Range("C33:34").Select
    Selection.ClearContents
    
    Range("C1").Select
    ActiveWorkbook.Save
    
End Sub
Liam Donovan
  • 23
  • 4
  • 3
    [This is the right way to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Jan 29 '21 at 15:48
  • 3
    Probably column A is empty and you are trying to go one row off the bottom of the worksheet. Also read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Jan 29 '21 at 15:49
  • 1
    I also do not think you can paste special values only with cut. I believe that will throw an error. – Scott Craner Jan 29 '21 at 15:57
  • Thanks for all the help everyone, this definitely solved my problems – Liam Donovan Jan 29 '21 at 16:42

0 Answers0