VBA export sheets from open workbook to closed workbooks

Hi gurus,

Thanks in advance to anyone who takes the time to respond!

I'm trying to create a macro which will loop through each sheet in a workbook called 'Syndication_Macro.xlsm' and copy the sheets to other workbooks.

The folderpath is the same for each workbook and the workbooks are named after the sheets to be exported. i.e.

Sheet 1 should be exported to:
"C:\Syndication\Sheet1.xlsx"

Sheet 2 should be exported to:
"C:\Syndication\Sheet2.xlsx"

etc. etc.

I haven't had much luck by myself so would appreciate any help!

Thanks for the code Greg, it worked for the first sheet but then resulted in an error at

Please Login or Register  to view this content.

for the second sheet.

Norie, I want to export the sheets to existing workbooks (as the last sheet). Apologies for not being more clear, would appreciate any help!

Any chance of getting this to work Norie or am I dreaming?