How to copy from a closed workbook to active workbook using vba?

[Siimo]

I am trying to copy data from a closed workbook from a specific sheet and from a specific range and paste it into my active workbook but into a specific sheet starting at cell B7. I have the below code however it is only returning zeros. can anyone point me in the right direction? I will be building on this code once I can get this working properly so i will essentially want it to copy a variety of ranges from different sheets form the closed workbook and paste into specific sheets and ranges etc.

The ranges from the closed workbook are also named so if i can include that it would also make it slightly quicker.

Sub RectangleRoundedCorners6_Click()Dim mydata As String
'data location & range to copy
mydata = "='C:\[1133.xlsm]Cars'!$B$5:$Y$141" '<< change as required


'link to worksheet
With ThisWorkbook.Worksheets("1133Cars").Range("B7") '<< change as required
.Formula = mydata
'convert formula to text
.Value = .Value


End With
End Sub

 

[JackDanIce]

You're welcome