Problem copying sheets to new file using vba

[uwajes]

Here's what I thought I had accomplished: Remote user has a file they update, as part of the closing the file out, part of it is published to a webpage on another server and the xls is also saved to another server. The macro below is for the saving it to another server as a different file name. It worked fine and then out of the blue when running the macro, I get a "Path/File Access Error".

The reason why I'm trying this method is that the original file has formulas and if you just copy/paste the cells to the new file, the formulas still reference the original file.

Anybody have a sample of vba that copys all the sheets of a worksheet and copies them to a blank one?

Please Login or Register  to view this content.

[whisperinghill]

For me, this opened a new Book1.xls and placed them in there and then saved as whatever I want it names in that directory. I hyiust recorded the Macro. In your Macro, you can specify any directory you want and filename

Please Login or Register  to view this content.

[Leith Ross]

Hello Uwajes,

Here is a macro to copy all the Worksheets from the Workbook the macro is run in, to a New Workbook. The macro names the New Workbook "Copy of" and the source Workbooks name. You can change that line of code to name the new workbook what you want.

Please Login or Register  to view this content.

Sincerely,
Leith Ross