Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Copying spreadsheet sheets to another

Posted on 2000-04-17
10
215 Views
Last Modified: 2010-05-02
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
Comment
Question by:robjay
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2724651
I think you can use Range,Copy,and Paste methods of WorkSheet object. Take a look at them.
0
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 2724917
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
 
LVL 3

Expert Comment

by:fibdev
ID: 2725108
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 22

Expert Comment

by:ture
ID: 2725707
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
 
LVL 22

Expert Comment

by:ture
ID: 2725735
robjay,

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

Worksheets.Copy

/Ture
0
 

Author Comment

by:robjay
ID: 2728475
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
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 2728856
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
 

Author Comment

by:robjay
ID: 2728904
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
 
LVL 22

Accepted Solution

by:
ture earned 50 total points
ID: 2729506
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
 

Author Comment

by:robjay
ID: 2730855
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question