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

How do I copy a sheet in Excel VBA from one workbook to another replacing it if it already exists.

Hi All,

I want to copy an Excel worksheet from workbook A to workbook B, checking to see if a sheet of that name already exists on workbook B. If it does, then I want to delete it, replacing it with the sheet from workbook A.

My question is: can you do this in one VBA statement ?

thanks
Toco
0
Tocogroup
Asked:
Tocogroup
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No - you have to do it with separate functions, because Microsoft does not want you to accidentally overwrite data and not be able to undo it.
0
 
NorieVBA ExpertCommented:
Toco

Short answer - no.

First you would need code to check if there was a worksheet in workbook B with the same name worksheet you want to copy from workbook A.

Then you would need code to delete it if required

Finally you do the copying.

The latter 2 are one-liners but the first part wouldn't be.
0
 
Rob HensonFinance AnalystCommented:
Can I suggest you confirm your understanding of the terminology?

One VBA Statement - do you mean one instruction / line within a routine or do you mean one set of instructions / lines making up a routine?

The way I try and understand a VBA routine is usually to imagine the thought process if you were doing it manually and then flow chart it. For your example, the steps I suspect would be:

Activate Source Workbook
Copy Data
Activate Destination Workbook
Q1 - Does required sheet Exist?
If Q1 = Yes - Q2 Does it already contain data?
If Q2 = Yes - Clear data and paste
If Q2 = No - Paste
If Q1 = No - Create Sheet, paste data.

Thanks
Rob H
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.

 
NorieVBA ExpertCommented:
Toco

Is it an entire sheet or is it data from a sheet you want to copy?
0
 
TocogroupAuthor Commented:
It's an entire sheet
0
 
Rob HensonFinance AnalystCommented:
I take it though if the sheet already exists it will be just data.

If so the check is simpler as you only have two possible actions, paste data or copy sheet.
0
 
TocogroupAuthor Commented:
Thanks for your joint help.

Yes, the steps are as follows:

1. Check if sheet exists in workbook.
2. If true, delete sheet.
3. Copy sheet
0
 
Rob HensonFinance AnalystCommented:
Rather than Delete sheet and then insert sheet, why not overwrite existing data with copied data.

In source files the following vba statement will copy the whole sheet:

Cells.Copy

Thanks
Rob
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

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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