Copy a sheet from one workbook and overwrite specific sheet in another

[Paulo H]

Hi
I have a workbook with several sheet in it, one being “Prices”

This needs to be updated and at the moment the prices are sent to me on another workbook

what I have to do is highlight the prices on the sheet on the master price list and copy then paste into the “Prices” sheet on my workbook

what I want is vba that will be available as a macro in my workbook to import the latest prices from the master prices sheet in the other workbook and overwrite the “Prices” in my workbook

suggestions please

 

[Paulo H]

Hi

The images are after the macro is run the first time it is clicked seem to create sheet6 then when clicked again it certainly resorts to sheet to which then is ok. What seems to work is to run the macro by clicking once then running again then all seems fine, its as if it pauses between creating Master prices and then deleting the old prices then renaming to Prices

 

[JoeMo]

Hi

The images are after the macro is run the first time it is clicked seem to create sheet6 then when clicked again it certainly resorts to sheet to which then is ok. What seems to work is to run the macro by clicking once then running again then all seems fine, its as if it pauses between creating Master prices and then deleting the old prices then renaming to Prices

After you run the first time, did you look at Sheet6 (tab name "Prices") to see if it has the updated content you are wanting? I may not have communicated the process the macro carries out well enough. Here's some additional information.

Let's say the destination workbook has a sheet tab name "Prices" and a sheet code name Sheet2 so it appears as Sheet2(Prices) in the VBE project screen. When you run the macro a new sheet with tab name "master prices" is copied to your workbook. Excel will assign to it a sequential code name Sheetx, so if there were ever only 5 sheets in your workbook, the code name and tab name both become Sheet6. Next, the macro deletes Sheet2(Prices). It then changes the tab name Sheet6 to Prices and is now Sheet6(Prices) in the VBE window. BUT, that's not the original Sheet2(Prices) sheet, its the updated info you want from the "master price list" workbook.

You should not have to run the macro again to provide that update.

 

[Paulo H]

Hi

yes I understand what is happening. The problem I have is that another macro I run seems to look for sheet2(prices) and throws an error.
As I mentioned when I run twice it seems to then reset the sheetx to sheet2 so will allow the other macro to work

Is there away to get it to default to sheet2?

 

[JoeMo]

Hi

yes I understand what is happening. The problem I have is that another macro I run seems to look for sheet2(prices) and throws an error.
As I mentioned when I run twice it seems to then reset the sheetx to sheet2 so will allow the other macro to work

Is there away to get it to default to sheet2?

Why not have the other macro look for a sheet with tab name "Prices"? No need to use the code name of that sheet if it will always be named "Prices". If you need help with that, post the other code and I will attempt to suggest a modification.

It would have been good to mention the impact of this other macro earlier - would have saved me the time spent retesting the code I gave you.

 

[Paulo H]

Hi
Sorted! change all the sheets2 to "Prices"

I thought I had change all these before but some were lost deep in the code

Many thanks for all your help

Brilliant

 

[JoeMo]

Hi
Sorted! change all the sheets2 to "Prices"

I thought I had change all these before but some were lost deep in the code

Many thanks for all your help

Brilliant

You are welcome - thanks for the reply.