Copy Active Worksheet into Multiple Workbooks in Separate Folder

I have been trying for two days to run a macro that will copy the active worksheet from one workbook into multiple workbooks that are in a separate folder. I'm unfamiliar with VBA and macros, so have been searching the web looking for code that will work. I've tried SEVERAL combos, but nothing is working. I either get a "Runtime Error 68" or "Compile Error Sub or Function not defined".

I'm using Excel 2010 on MacOS Mavericks. Any guidance would be most appreciated.


Here's the most recent code I've been trying:

Option Explicit

Public Sub CopySheetToAllWorkbooksInFolder()

Dim sourceSheet As Worksheet
Dim folder As String, FileName As String
Dim destinationWorkbook As Workbook

'Worksheet in active workbook to be copied as a new sheet to the 160 workbooks

Set sourceSheet = ActiveWorkbook.Worksheets("Updates to Existing Copy")

'Folder containing the 160 workbooks

folder = "ENTER FOLDER NAME HERE" 'Note: I have updated as necessary but left for purposes of this question.

FileName = Dir(folder)
While Len(FileName) <> 0
Debug.Print folder & FileName
Set destinationWorkbook = Workbooks.Open(folder & FileName)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
FileName = Dir() ' Get next matching file
Wend

End Sub

@ FinkKosek.
Please read the Forum Rules. In your case pay special attention to #3.

I have been trying for two days to run a macro that will copy the active worksheet from one workbook into multiple workbooks that are in a separate folder. I'm unfamiliar with VBA and macros, so have been searching the web looking for code that will work. I've tried SEVERAL combos, but nothing is working. I either get a "Runtime Error 68" or "Compile Error Sub or Function not defined".

I'm using Excel 2010 on MacOS Mavericks. Any guidance would be most appreciated.


Here's the most recent code I've been trying:

Please Login or Register  to view this content.