PDA

View Full Version : VBA to copy a Specif sheet from multiple other workbooks in MAC



cofi
10-25-2018, 01:38 PM
Hi,

I'm trying to get a VBA to work in Excel for MAC that when I press a button it asks me to choose the files I want and then from each of them import the sheet "1-Summary" to my active workbook.

I found, here in this forum, the bellow VBA witch is perfect for me. The problem is that "Application.Workbooks.Open" doesn't work in MAC, because it has to be paired watch scripting.
Can anyone help on how to make this work in Excel for MAC?

Thanks

Sub ImportSheet()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
If sImportFile = "False" Then
MsgBox "No File Selected!"
Exit Sub

Else
vfilename = Split(sImportFile, "")
sFile = vfilename(UBound(vfilename))
Application.Workbooks.Open Filename:=sImportFile

Set wbBk = Workbooks(sFile)
With wbBk
If SheetExists("1-Summary") Then
Set wsSht = .Sheets("1-Summary")
wsSht.Copy before:=sThisBk.Sheets("Output")
Else
MsgBox "There is no sheet with name :1-Summary in:" & vbCr & .Name
End If
wbBk.Close SaveChanges:=False
End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Function SheetExists(sWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sWSName)
If Not ws Is Nothing Then SheetExists = True
End Function