Solved

Copying spreadsheet sheets to another

Posted on 2000-04-17
10
221 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
[X]
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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

749 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