Copy cell range between workbooks from same folder

Question

I currently have:

Sub Ranger()
Dim rng As Range
Dim WB1 As Workbook, WB2 As Workbook, ActiveWB As Workbook
Dim WS1 As Worksheet, WS2 As Worksheet
Dim FName As String

FName = "General Text"
Set WB1 = ThisWorkbook
Set WS1 = WB1.Sheets("Sheet1")
Set WB2 = Workbooks.Open(FileName:=FName)
Set WS2 = WB2.Sheets(1)

With WS2
    Set rng = .Range(Range("A1"), Range("A5"))
    End With

With WS1
    rng.Copy .Cells(1, 1)
    End With

WB2.Close
End Sub

Which aims to copy the range A1:A5 in the newly opened workbook into the original workbook (ThisWorkbook). It currently opens the second workbook but does not copy anything into the first workbook. There are also no errors and I would like to avoid using specific names in setting WB1/WB2 as WB2 could be either .xls or .xlsx

Way overcomplicated, but should work... Try simply making the ranges equal instead of .Copy. — vacip

vacip vacip · Accepted Answer · 2016-12-03T12:05:37

Try this, it works for me:

Sub Ranger()

  Dim rng As Range
  Dim WB2 As Workbook
  Dim FName As String

  FName = "D:\Tuchanka\Temp\pelda.xlsx"
  Set WB2 = Workbooks.Open(Filename:=FName)

  ThisWorkbook.Worksheets(1).Range("A1:A5").Value = WB2.Worksheets(1).Range("A1:A5").Value

  WB2.Close
End Sub

Using variables and with statements is pointless in your situation, as instead of making your code simpler and easier to read, they just add a lot of gibberish and make your code seem way too complex. Only use these if you need them.

Copy cell range between workbooks from same folder

1 Answers