• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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.
0
robjay
Asked:
robjay
  • 3
  • 3
  • 2
  • +2
1 Solution
 
MarineCommented:
I think you can use Range,Copy,and Paste methods of WorkSheet object. Take a look at them.
0
 
Glen RichmondCustomer Reporting Programmer.Commented:
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
0
 
fibdevCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tureCommented:
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
0
 
tureCommented:
robjay,

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

Worksheets.Copy

/Ture
0
 
robjayAuthor Commented:
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)

0
 
Glen RichmondCustomer Reporting Programmer.Commented:
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
0
 
robjayAuthor Commented:
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.  
0
 
tureCommented:
robjay,

'This copies all worksheets to a new workbook, which becomes the active workbook
Worksheets.Copy

'This saves the active workbook
ActiveWorkbook.SaveAs :="C:\webdir\" & FileVar

'And if you wish, you may close the workbook afterwards
ActiveWorkbook.Close

/Ture
0
 
robjayAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now