workbooks.add type mismatch error on mac

%3CLINGO-SUB%20id%3D%22lingo-sub-908173%22%20slang%3D%22en-US%22%3Eworkbooks.add%20type%20mismatch%20error%20on%20mac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908173%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20number%20of%20spreadsheets%20that%20are%20used%20on%20both%20windows%20and%20mac%2C%20I%20have%20two%20public%20procedures%20that%20create%20a%20second%20workbook%20and%20copy%20some%20content%2C%20they%20have%20a%20similar%20construct%3C%2FP%3E%3CP%3Eie.%3C%2FP%3E%3CP%3EPublic%20Sub%20GenerateWeeklyReport()%3CBR%20%2F%3E%26nbsp%3B%20Dim%20SourceWB%20As%20String%3CBR%20%2F%3E%26nbsp%3B%20Dim%20TargetWB%20As%20String%3CBR%20%2F%3E%26nbsp%3B%20Dim%20WB%20As%20Workbook%3CBR%20%2F%3E%26nbsp%3B%20Let%20SourceWB%20%3D%20ActiveWorkbook.Name%3CBR%20%2F%3E%26nbsp%3B%20Set%20WB%20%3D%20Workbooks.Add%3CBR%20%2F%3E%26nbsp%3B%20Let%20TargetWB%20%3D%20ActiveWorkbook.Name%3CBR%20%2F%3E%26nbsp%3B%20Application.ScreenUpdating%20%3D%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%20Application.DisplayAlerts%20%3D%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%20--Copies%20stuff%20between%20SourceWB%20and%20TragetWB%3C%2FP%3E%3CP%3E%26nbsp%3B%20Application.DisplayAlerts%20%3D%20True%3C%2FP%3E%3CP%3E%26nbsp%3B%20Application.ScreenUpdating%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20Macros%20work%20perfectly%20on%20Windows%2C%20one%20macro%20works%20properly%20on%20mac%2C%20however%20the%20second%20macro%20(Both%20in%20the%20same%20module)%2C%20produces%20a%20type%20mismatch%20error%20on%20set%20WB%3Dworkbooks.add%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E---I%20have%20just%20moved%20the%20second%20sub%20to%20its%20own%20module%20and%20now%20it%20works%20ok...could%20this%20be%20yet%20another%20mac%20feature%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-908173%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

I have a number of spreadsheets that are used on both windows and mac, I have two public procedures that create a second workbook and copy some content, they have a similar construct

ie.

Public Sub GenerateWeeklyReport()
  Dim SourceWB As String
  Dim TargetWB As String
  Dim WB As Workbook
  Let SourceWB = ActiveWorkbook.Name
  Set WB = Workbooks.Add
  Let TargetWB = ActiveWorkbook.Name
  Application.ScreenUpdating = False

  Application.DisplayAlerts = False

  --Copies stuff between SourceWB and TragetWB

  Application.DisplayAlerts = True

  Application.ScreenUpdating = True

End Sub

 

Both Macros work perfectly on Windows, one macro works properly on mac, however the second macro (Both in the same module), produces a type mismatch error on set WB=workbooks.add

 

---I have just moved the second sub to its own module and now it works ok...could this be yet another mac feature?

0 Replies