Excel VBA to copy and paste as values from a closed workbook to an active workbook, Mac OS X

Question

I know this is a common type of query, but I have not been able to find an answer yet to my specific instance after active searching. I am also a new VBA user, so please bear with me.

I want to import an entire sheet of data (AllDATA tab in HISTORY.XLSM) that contains live formulae (range A1:HW6000) from a closed workbook into an active workbook, but only paste as values into the active workbook. More simply, want to replicate one sheet from the closed workbook as values (not live formuale) into the active worksheet.

I am on Mac OS X.

This is the code that I have been working with (running the code from the active workbook).

Sub GetRange() 
  With Range("A1:HW6000") 
    .Formula = "='C:\[HISTORY.xlsm]ALLDATA'!A1" 
    .Value = .Value 
End With 
End Sub

This code seems to import formats but not actual pasted values into my active workbook.

Any ideas?

Pᴇʜ Pᴇʜ · Accepted Answer · 2017-04-12T08:37:17

You should do a real copy-paste like that

Sub ImportData()
    Dim App As New Excel.Application 'create a new (hidden) Excel

    ' remember active sheet
    Dim wsActive As Worksheet
    Set wsActive = ThisWorkbook.ActiveSheet

    ' open the import workbook in new Excel (as read only)
    Dim wbImport As Workbook
    Set wbImport = App.Workbooks.Open(Filename:="C:\History.xlsm", UpdateLinks:=True, ReadOnly:=True)

    'copy the data of the import sheet
    wbImport.Worksheets("AllDATA").Range("A1:HW6000").Copy
    wsActive.Range("A1").PasteSpecial Paste:=xlPasteFormats 'paste formats
    wsActive.Range("A1").PasteSpecial Paste:=xlPasteValues  'paste values

    App.CutCopyMode = False 'clear clipboard (prevents asking when wb is closed)
    wbImport.Close SaveChanges:=False 'close wb without saving
    App.Quit 'quit the hidden Excel
End Sub

Excel VBA to copy and paste as values from a closed workbook to an active workbook, Mac OS X

1 Answers