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.
Thanks in advance.
I think you can use Range,Copy,and Paste methods of WorkSheet object. Take a look at them.
you could try using
FileCopy "FileToCopyPathAndName","D estination FilePathAn dName"
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
FileCopy "FileToCopyPathAndName","D
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.
<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
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
This line copies all worksheets in the active workbook to a new workbook.
Worksheets.Copy
/Ture
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)
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","D estination FilePathAn dName"
you dont need anyother object variables or open excel just path and name.. try it
FileCopy "FileToCopyPathAndName","D
you dont need anyother object variables or open excel just path and name.. try it
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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