0

I have a workbook that refreshes data connections, then filters dates from yesterday. When there is only one row that is refreshed, I will get a

Run time error 1004.

Some days the macro works, some days it doesn't. This is run daily. 

Sub Get_VRIDs()    
    Dim i As Integer
    Sheets("Cancels").Select
    i = 1

    With Range("E2")
    If .Cells(1, 1).Value = "" Then
    Else
    Range(.Cells(1, 1), .End(xlDown)).Copy 
    Destination:=Sheets("Metric").Range("a6")
     x = x + 1

    End If
    End With

    Sheets("Adhoc").Select
    i = 1

With Range("C2")
   If .Cells(1, 1).Value = "" Then
   Else
     Range(.Cells(1, 1), .End(xlDown)).Copy Destination:=Sheets("Metric").Range("a94")
     x = x + 1

   End If
End With

Sheets("Direct Tender").Select
i = 1

    With Range("B2")

   If .Cells(1, 1).Value = "" Then
   Else
     Range(.Cells(1, 1), .End(xlDown)).Copy Destination:=Sheets("Metric").Range("a132")
     x = x + 1

  Sheets("Metric").Activate
   End If
End With

Sheet1.Activate
    Range("B6").Select
    Selection.Copy
    Range("A6:A90").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=48
    Range("B60").Select
    Selection.Copy
    Range("A94:A128").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=24
    Range("B90").Select
    Selection.Copy
    Range("A132:A200").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-108
    Range("A7").Select

    Call Hide_Rows

End Sub

The error occurs at this line:

  Range(.Cells(1, 1), .End(xlDown)).Copy Destination:=Sheets("Metric").Range("a94")
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dan Martinez
  • 1
  • 2
    If there is only one row of data the `.End(xlDown)` will go to the bottom of the page and try to paste an area that is too large to fit in an area starting at row 94. – Scott Craner Jan 22 '19 at 18:46
  • Makes sense, how can it be fixed? Ill need it to work when there is 1 row, 40, or even none. – Dan Martinez Jan 22 '19 at 18:52
  • 1
    Solution is to use `.End(xlUp)` from the bottom of the sheet. Also, you will benefit from [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – chris neilsen Jan 22 '19 at 18:57
  • This doesn't explain how some days, the macro will copy just the one row and not error out. Im not sure how that is. – Dan Martinez Jan 22 '19 at 19:31

0 Answers0