Run-time error '1004' When Using Excel-VBA Macro to Copy From Workbook

Question

I'm trying to implement a simple Excel-VBA Macro to have the user browse for another workbook in the file explorer, and then have certain cells in that workbook copied into my active workbook. Here's my short code:

Sub Load_AutoCADBOM()

   Dim wbk As Workbook

   Dim MyFile As String
   MyFile = Application.GetOpenFilename()

   If MyFile <> "False" Then
      Set wbk = Workbooks.Open(MyFile)
      With wbk.Sheets(1)
         Range("B2:C43").Copy
      End With

  ActiveWorkbook.Close


  With ThisWorkbook.Worksheets("Config")
     Range("A6:B47").PasteSpecial Paste:=xlPasteValues
  End With
   End If

End Sub

The macro is meant to copy cells from (B2:C43) from the selected workbook and copy them into cells (A6:B47) on sheet "Config" of my current workbook. When I run the macro I get "Run-time error '1004': PasteSpecial method of Range class failed." The debugger highlights the line:

     Range("A6:B47").PasteSpecial Paste:=xlPasteValues

I've tried copying from csv, xls & xlsm files all with the same result. Could it possibly be the way my cells are formatted in the sheet I'm pasting to? The funny this is I've used this macro in another workbook and had it work no problem.

If anyone knows of any way I can fix my code and get it working, it would be much appreciated.

Thankyou

End Sub

When using a With block you need to use a leading period to bind objects to the scope of the With, so .Range("A6:B47") for example — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2018-10-18T20:03:57

If you just want to copy values then you can skip the copy/paste and set the values directly from the source range:

Sub Load_AutoCADBOM()

   Dim wbk As Workbook
   Dim MyFile As String
   MyFile = Application.GetOpenFilename()

   If MyFile <> "False" Then
        Set wbk = Workbooks.Open(MyFile)
        With wbk.Sheets(1).Range("B2:C43")
            ThisWorkbook.Worksheets("Config").Range("A6").Resize(.rows.count, .columns.count).value = .Value
        End With
        wbk.Close
    End If

End Sub

Run-time error '1004' When Using Excel-VBA Macro to Copy From Workbook

1 Answers