Trying to copy data from multiple sheets one by one and paste to a different sheet.

Question

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

Answer 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