Solved

Copying spreadsheet sheets to another

Posted on 2000-04-17
10
188 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 22

Expert Comment

by:ture
Comment Utility
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
Comment Utility
robjay,

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

Worksheets.Copy

/Ture
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:robjay
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now