Import Macro and personal.xlsb

[kmmsquared]

I am new to macros and trying to get this to pull data from another file, but it only imports the data into my personal.xls sheet and not the active sheet. I have checked that it is under a general module and not a worksheet in the personal.xls, but can't get it to work.

Suggestions?


Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set Thiswb = Workbooks.Open("FileName", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
.Range("A1:C29").Formula = Thiswb.Worksheets("Sheet1").Range("A1:C29").Formula
End With
Thiswb.Close False ' close the source workbook without saving any changes
Set Thiswb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Thanks

 

[kmmsquared]

I have the code in my personal.xlsb so I can run it in any workbook, the problem is the import is done in the personal.xlsb and not whatever workbook is currently active, even if i run the macro from book1 the data will still import into my personal.xlsb

 

[wigi]

For instance:

Sub GetDataFromClosedWorkbook()
    Dim wbSource As Workbook, wbTarget As Workbook
    Application.ScreenUpdating = False    ' turn off the screen updating
    Set wbTarget = ActiveWorkbook
    Set wbSource = Workbooks.Open("FileName", True, True)
    ' open the source workbook, read only
    wbTarget.Worksheets("Sheet1").Range("A1:C29").Formula = wbSource.Worksheets("Sheet1").Range("A1:C29").Formula
    wbSource.Close 0    ' close the source workbook without saving any changes
    Set wbSource = Nothing: Set wbTarget = Nothing
End Sub

 

[kmmsquared]

yes! genius! thank you! :D