MODIFY MACRO to value out columns with specified headers!

[fisht]

Hi All-

I have a macro that I currently use to save and value out a worksheet to a new workbook. I need some help in adjusting the macro to only value out columns with specific headers in row 24. For example: if the row headers say either "cost", "revenue", and "profit" (no matter what column), I would like to the macro to only value out those columns when saving the worksheet out to a new workbook.

Please help!!

Below is the current code:

Sub savePSh()
Dim sh As Worksheet
ActiveWorkbook.Sheets("P&L").Copy
Set sh = ActiveWorkbook.Sheets(1)
sh.Name = sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
With sh.UsedRange.Cells
.Value = .Value
End With
ActiveWorkbook.SaveAs "c:\" & sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
ActiveWorkbook.Close False
End Sub

 

[dermie_72]

This tested out ok on the valuing part, so it should be OK. Let me know how it goes.

Sub fisht()
'for Sub savePSh()
Dim sh As Worksheet
Dim LR As Long
Dim LC As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row + 1
LC = ActiveSheet.UsedRange.Columns.Count
ActiveWorkbook.Sheets("P&L").Copy
Set sh = ActiveWorkbook.Sheets(1)
sh.name = sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
For I = 1 To LC
    If Cells(24, I).Value = "Cost" Or Cells(24, I).Value = "Revenue" Or Cells(24, I).Value = "Profit" Then
        Else:   With Cells(24, I).Resize(LR - 24)
                    .Value = .Value
                End With
    End If
Next I
ActiveWorkbook.SaveAs "c:\" & sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
ActiveWorkbook.Close False
End Sub

 

[dermie_72]

Im guessing that because you haven't replied, the code works great.