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
TocogroupAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
NorieAnalyst Assistant Commented:
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.