We help IT Professionals succeed at work.

VBA Script to copy a worksheet to another workbook on close

NLITech
NLITech asked
on
273 Views
Last Modified: 2012-06-27
I want to start by saying I know nothing about writing a vba script.
I have an Excel 2007 workbook named Duty_Roster.xlsx; it has nine worksheets in it. One of the worksheets is named Duty Supervisor On Call. I would like to have a vba script that on close of Duty_Roster it would save a copy of the worksheet Duty Supervisor On Call to a workbook named Duty Supervisor On Call that would be used by users using Excel 2003. The Duty Supervisor On Call workbook will only have one worksheet also named Duty Supervisor On Call that would be re-written on close of Duty_Roster.
Thank you for your help.
Lori
Comment
Watch Question

Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Would it be OK to simply delete the old workbook and create a new one from the worksheet, or do you need to save anything in the old workbook?
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Dave,
I do not know what I am doing wrong. I did put you code in the workbook in the ThisWorkbook. I am getting a Run-time error 9 Subscript out of range error. When I debug the ("Duty Supervisor On Call").Copy is hi-lighted

Author

Commented:
Rorya,
Yes, it would be ok to delete the old Duty Supervisor On Call  workbook.
Lori
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Dave and rorya,
You are both brilliant. Rorya, you were right the name of the worksheet did have a space following the name. I did divide the points as you both helped me. Thank you!
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Very kind of you! :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.