We have a component finish (Powdercoat, Anodise, Electroplate etc) Excel sheet that purchasing use to help parts got through the correct processes.
Input the part no and the rest auto populates.
We need to output a new worksheet for suppliers use in which we’d like to maintain the formating, but exclude the formulae just displaying the populated value/text.
Managed to get it to output with a two part copy/paste process, but it keeps dragging the formulae through.
Now stumped and google-fu has run out – VBA below:
Sub Output()
Dim WB_SourceFile As Workbook
Dim WB_New As Workbook
Set WB_SourceFile = Workbooks(“Powder Coat Reference Sheet (version 3.2).xlsm”)
WB_SourceFile.Worksheets(“Colour Finder”).Range(“D1:N19”).Copy
Set WB_New = Workbooks.Add
WB_New.ActiveSheet.Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
WB_SourceFile.Worksheets(“Colour Finder”).Range(“U20:AA49”).Copy
WB_New.ActiveSheet.Range(“B20”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End Sub