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]

Note comments below in code and try:

Sub M1()
        
    Dim wkb     As Workbook
    Dim sFile   As String
    
    sFile = "C:\1133.xlsm" 'The full path should go here
    
    Application.ScreenUpdating = False
    
    Set wkb = Workbooks.Open(sFile, ReadOnly:=True)
    
    With wkb
        'Cell (7,2) = row 7, column 2, i.e. B7
        sheets("1133Cars").Cells(7, 2).Resize(137, 24).Value = .sheets("Cars").Cells(5, 2).Resize(137, 24).Value
        .Close False
    End With
    
    ThisWorkbook.Activate
    sheets("1133Cars").Select
    
    Application.ScreenUpdating = True
    
    Set wkb = Nothing
    
End Sub

 

[Siimo]

Thank you for the reply Jack, I am receiving an error on the below line.

Set wkb = Workbooks.Open(sFile, ReadOnly:=True)

I believe this is due to it not finding the destination of the file. my destination is;

sFile = "C:\Users\Siimo\Documents\Group Tracking EoTF\[1133.xls]"

Which is what I have put in but it is still not finding this apparently.

 

[JackDanIce]

Try:

sFile = "C:\Users\Siimo\Documents\Group Tracking EoTF\1133.xlsx"

It's exactly the same path as in the Windows Explorer, not sure why you're including [ and ] around the file name.

 

[Siimo]

I have fixed the issue of finding the document. It is now bringing the below up as a debug;

Sheets("1133Cars").Cells(7, 2).Resize(137, 24).Value = .Sheets("Cars").Cells(5, 2).Resize(137, 24).Value

This could be due to the sheet it is copying from is a hidden sheet? If so could this be worked around?

 

[JackDanIce]

Change that line to:

     With sheets("1133Cars")
        .Hidden = xlSheetVisible
        .Cells(7, 2).Resize(137, 24).Value = .sheets("Cars").Cells(5, 2).Resize(137, 24).Value
    End With

 

[Siimo]

I get Run Time Error 438 (Object doesn't support this property or method)

debug shows the below line;

.Hidden = xlSheetVisible

 

[Siimo]

for previous error I changed the code around

[COLOR=#333333]With sheets("Cars")[/COLOR]        .Hidden = xlSheetVisible
        .Cells(7, 2).Resize(137, 24).Value = .sheets("1133Cars").Cells(5, 2).Resize(137, 24).Value [COLOR=#333333]    End With[/COLOR]

The exact code you gave me gave me an error of out of range and debug was showing the below line;

With Sheets("1133Cars")

 

[JackDanIce]

Shouldn't be a problem copying from a hidden sheet, the sheet is hidden but the cells of that sheet still contain values.

Without your workbook in front of me, difficult to decipher code changes, what is going on and what is described.

 

[Siimo]

Thank you for your help I appreciate it, I will have a play around with the coding and see if I can work it out.