John has attended:
Excel VBA Intro Intermediate course
Excel VBA
I have a spreadsheet with several columns with a header called'Result' where data is calculated (using formula)
How to copy and paste special - values data in the same columns? The code should be able to run for all columns with a header.
RE: Excel VBA
Hi John,
Thank you for your question.
Assuming your worksheet name is Sheet1 and the header row is on row 1, you can try the following code:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub PasteValue()
Dim Columns As Byte
Dim Counter As Byte
Sheets("Sheet1").Select 'Make sure you are on the correct sheet
Range("A1").Select 'Start of header row
Columns = Range(Selection, Selection.End(xlToRight)).Columns.Count 'Find no. of Heading labels
For Counter = 1 To Columns
If ActiveCell.Value = "Results" Then
ActiveCell.Offset(1, 0).Activate 'Select 1st result formula
Range(Selection, Selection.End(xlDown)).Copy 'highlight till the last result formula
Selection.PasteSpecial Paste:=xlPasteValues
Selection.End(xlUp).Select 'Return to Header row
End If
ActiveCell.Offset(0, 1).Activate 'Try next header
Next Counter
Application.CutCopyMode = False
Range("A1").Select 'Return to the beginning
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
See if that helps.
Katie Woo
Microsoft Certified Trainer
