Link to home
Start Free TrialLog in
Avatar of robjay
robjay

asked on

Copying spreadsheet sheets to another

I need a quick way to create another spreadsheet and copy over worksheets from an original.  I'm trying to leave off all my VB code on the original which takes a long time to copy anyway and has Open_File code I don't want executed in the "copy".

Thanks in advance.
Avatar of Marine
Marine

I think you can use Range,Copy,and Paste methods of WorkSheet object. Take a look at them.
you could try using

FileCopy "FileToCopyPathAndName","DestinationFilePathAndName"

this will copy the file exactly asis to a new name and location, check out your object browser, look for FileSystem.This offers quite a few file functions.

Good luck
Marine's suggestion gives you more control but glenrichmond's is quicker with less code.

<shrug> depends on how much you want out of it.
robjay,

This single line of code copies a worksheet and pastes it into a new workbook (with only this sheet in it)  which becomes the active workbook.

  Sheets("Sheet1").Copy

Ture Magnusson
Karlstad, Sweden
robjay,

This line copies all worksheets in the active workbook to a new workbook.

Worksheets.Copy

/Ture
Avatar of robjay

ASKER

Ture,  I've skimmed two Excel/VB reference books and have been unable to find this used - I'm trying:

Worksheets.Copy FileName:="C:\webdir\" & FileVar, FileFormat:=xlNormal

I get an error on FileName, named argument not found

(FileVar is the name of my xls file that I dynamically put together in the code)

just use my previous comment then to copy

FileCopy "FileToCopyPathAndName","DestinationFilePathAndName"

you dont need anyother object variables or open excel just path and name.. try it
Avatar of robjay

ASKER

Thanks gr, but this does not work for my ap.
I need to copy only the worksheets, not the VB Modules behind them and I need to copy over the present state, not the saved state.  
ASKER CERTIFIED SOLUTION
Avatar of ture
ture

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of robjay

ASKER

Works like a champ Ture, just a little error in your code where you left out FileName -
So future seekers of this same answer - these three lines of code will copy your current worksheets (no modules) and becomes the active workbook, saves this workbook, then closes this active workbook and restores you to your original worksheet:

Worksheets.Copy
ActiveWorkbook.SaveAs FileName:"C:\webdir\" & FileVar
ActiveWorkbook.Close