VBA Code to Copy a Sheet from Workbook A and paste it on that sheet in Workbook B

[UncleBajubjubs]

Hello, I have a large workbook which does a ton of engineering calculations to select which parts to use in a construction product. One sheet in this workbook contains a list of parts, which I'd like to copy to another workbook which handles the pricing and ordering.

Essentially, I want a macro to copy 'Bill of Materials' from 'Engineering Workbook' and have it paste it over 'Bill of Materials' in 'Purchasing Workbook'. There's only two named ranged that need to be copied over, and it doesn't need the formulas to still work, just their values (so, if a cell contains formula '3*4', i just want to copy over '12'). How would I go about doing this in the simplest way possible.

Thanks.

 

[frank_AL]

Give this code a try. Let me know if there are any issues with it.
You will need to edit this line of code: fileloc = "C:". Enter the folder location where the file "Engineering Workbook" exists.

Option Explicit


Sub CopyMaterials()
Dim cfwb As Workbook
Dim fileloc As String


Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Sheets("Bill of Materials").UsedRange.Clear
Application.DisplayAlerts = True


[COLOR=#ff0000]fileloc = "C:\"[/COLOR]
Workbooks.Open fileloc & "Engineering Workbook"
Set cfwb = Workbooks("Engineering Workbook")
cfwb.Sheets("Bill of Materials").UsedRange.Copy
ThisWorkbook.Sheets("Bill of Materials").Range("A1").PasteSpecial xlPasteValues
End Sub
'