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

Paulo H

Board Regular
Joined
Jun 10, 2020
Messages
106
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I suggest you provide sufficient information so someone can produce the VBA solution you want. That would include details about where on the prices sheet the prices reside (what range do they occupy?), More details about "the other workbook" that's sent to you like name, relevant sheet name and location of the prices on same. Will the "other workbook" always be open when the VBA is executed? Use XL2BB to post some sample data, so we can see the data layout on both the source sheet and the destination sheet ...
 
Upvote 0
Hi

The other wirkbook is called “master price list” and isn’t always open! the entire sheet would be copied and would overwrite the entire “Prices”sheet in my workbook
 
Upvote 0
Hi

The other wirkbook is called “master price list” and isn’t always open! the entire sheet would be copied and would overwrite the entire “Prices”sheet in my workbook
Good start, but we still need more info. Is the other workbook in the same folder as your workbook? If not, what's the path to the other workbook? Also, what's the file extension of the other workbook?
 
Upvote 0
The sheet It is just called “master price”

thanks for your help
 
Upvote 0
This macro should be installed as a standard module in your workbook. It copies the sheet named "master price" from the workbook "master price list" to the workbook the macro is in and renames if "Prices" after the existing sheet named "Prices" is deleted. As written, the macro does not save or close either workbook, but that can easily be done.
VBA Code:
Sub PauloH()
Dim WbS As Workbook, WbD As Workbook, WbSName As String
Application.ScreenUpdating = False
WbSName = "master price list.xlsm"
Set WbD = ActiveWorkbook
If Not WorkbookOpen(WbSName) Then
    Set WbS = Workbooks.Open(WbD.Path & Application.PathSeparator & WbSName)
Else
    Set WbS = Workbooks(WbSName)
End If
WbS.Sheets("master price").Copy before:=WbD.Sheets("Prices")
With WbD
    Application.DisplayAlerts = False
    .Sheets("Prices").Delete
    Application.DisplayAlerts = True
    .Sheets("master price").Name = "Prices"
End With
Application.ScreenUpdating = True
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function
 
Upvote 0
Hi

Thank you for this brilliant. It works well but seems to have a slight issue

Someties it overwrites the Prices which is good but sometimes it creates new sheet with same name

I looked in VBA and it works with the top example but errors with second

I think but am not sure that if you get the error but run the macro again it seems to work. the Sheet2(Prices) is good but fails when Sheet6(Prices) it is always these to sheets listed.
Many thanks again Paul

1605868106426.png
 
Upvote 0
Hi

Thank you for this brilliant. It works well but seems to have a slight issue

Someties it overwrites the Prices which is good but sometimes it creates new sheet with same name

I looked in VBA and it works with the top example but errors with second

I think but am not sure that if you get the error but run the macro again it seems to work. the Sheet2(Prices) is good but fails when Sheet6(Prices) it is always these to sheets listed.
Many thanks again Paul

View attachment 26430
Please read the description of what the macro does in post #8. There is no overwrite of any sheet. First, the sheet "master price" is copied to the destination workbook (with the tab name "master price"). Then the existing sheet with tab name "Prices" is deleted from the destination workbook, and ,finally, the tab name of the copied sheet is changed from "master price" to "Prices". In my tests this seems to work well.

I don't understand the difference between your two examples. There can only be one sheet with tab name "Prices" in the destination workbook. Are the images you posted before or after the macro is executed?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top