0

I am trying to copy data from multiple sheets one by one and paste them to a different sheet. Here is my code below. However, it shows "Run-time error 1004: Select method of Range class failed". 

Private Sub CommandButton1_Click()
'Stop if report not filled in fully
If Range("G28") < "1" Then
MsgBox ("Please Amend Quantity")
End If



 If Range("G28") >= "1" Then

  Range("B28").Select
  Selection.Copy
  Sheets("Order List").Range("A1").Offset(1, 0).Select
  ActiveSheet.Paste

 End If

 Sheets("Order List").Columns("A:D").AutoFit



End Sub

The code: "Sheets("Order List").Range("A1").Offset(1,0).Select" is highlighted error. I was trying to let it select the blank column in Line A from A1 under Order List sheet and paste the value from B28 under current sheet to it. Please give me some advice. Many thanks

J Ding
  • 33
  • 6
  • Is the Order List sheet hidden, by chance? Try `Range("B28").Copy Sheets("Order List").Range("A1").Offset(1,0)` inside of your `If Range("G28") >= "1"` if instead of what's in there now. – barvobot Oct 18 '17 at 14:52
  • You cannot select a cell on a sheet which is inactive. – SJR Oct 18 '17 at 15:00

1 Answers1

1

Don't use .Select or .Activate. That's what's causing your problem. See this explanation for how to avoid it and why: How to avoid using Select in Excel VBA

Here's an updated version of your code which should work for you:

Private Sub CommandButton1_Click()

    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet

    Set wb = ActiveWorkbook
    Set wsSource = wb.Sheets("Sheet1")  'Set to correct source worksheet name
    Set wsDest = wb.Sheets("Order List")

    'Stop if report not filled in fully
    If wsSource.Range("G28").Value < 1 Then
        MsgBox "Please Amend Quantity"
        Exit Sub
    End If

    wsSource.Range("B28").Copy wsDest.Range("A2")
    wsDest.Range("A:D").EntireColumn.AutoFit

End Sub
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • Thank you very much. However, if I click button again for other lines, will it paste to A3 rather than replacing A2? – J Ding Oct 18 '17 at 15:03
  • @JDing It will not. That logic wasn't in your original code. I encourage you to edit what I've provided to include that functionality. Create a new question if you get stuck. – tigeravatar Oct 18 '17 at 15:04