Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
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
Try this
Sub Output()
Dim WB_SourceFile As Workbook
Dim WB_New As Workbook
Set WB_SourceFile = Workbooks("Powder Coat Reference Sheet (version 3.2).xlsm")
Set WB_New = Workbooks.Add
' Copy and paste the first range with column widths and values
WB_SourceFile.Worksheets("Colour Finder").Range("D1:N19").Copy
WB_New.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
WB_New.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
' Copy and paste the second range with values
WB_SourceFile.Worksheets("Colour Finder").Range("U20:AA49").Copy
WB_New.Sheets(1).Range("B20").PasteSpecial Paste:=xlPasteValues
' Clear clipboard
Application.CutCopyMode = False
End Sub
In this modified code, I separated the paste operations for column widths and values, and I added PasteSpecial with xlPasteValues to ensure that only values are pasted. Additionally, I included Application.CutCopyMode = False at the end to clear the clipboard, which is good practice after using copy and paste operations.
For simple tasks like this.
Just record the macro by hitting the record button bottom left and carrying out the action as you want it wo work.
Then go and look at the macro and see if you need to edit it.
I blew an IT guys mind when I just recorded a macro to insert a copied row at the top of a sheet rather than his attempt o macro a solution to find the first empty row and paste it there.
Thanks, oldtennisshoes your code through up a couple of errors I struggled to resolve, but adding "Application.CutCopyMode = False" to the end of the original code solved the issue.