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]

do a match to find the column number, then use that variable to select the column/range to value.
You may need a lastrow variable as well, either for the last row, or for a resize on the range.

 

[fisht]

Thanks for the response Dermie. Unfortunately, I am not that fluent in VBA.. would you be able to amend the code I provided to do what you suggested?

 

[dermie_72]

you would need to specify what the criteria is for the column. What is the exact text you're looking for to sum the column with.
BTW...about to head to bed, so ill provide the answer tomorrow in the morning, but I need that information before I can modify anything.

 

[fisht]

The specific column criteria is: "Cost", "Revenue", & "Profit".

 

[fisht]

The specific column criteria is: "Cost", "Revenue", & "Profit".

Anyone else have the ability to amend the aforementioned VBA code?

 

[dermie_72]

this code worked when I tested it.
let me know the outcome please..

Sub fisht()
'for Sub savePSh()
Dim sh As Worksheet
Dim LR As Long
Dim Cost As Integer, Revenue As Integer, Profit As Integer
ActiveWorkbook.Sheets("P&L").Copy
Set sh = ActiveWorkbook.Sheets(1)
sh.name = sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
Cost = Application.WorksheetFunction.Match("Cost", Range("1:1"), 0)
Revenue = Application.WorksheetFunction.Match("Revenue", sh.Range("1:1"), 0)
Profit = Application.WorksheetFunction.Match("Profit", sh.Range("1:1"), 0)
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Cells(2, Cost).Resize(LR - 1)
    .Value = .Value
End With
With Cells(2, Revenue).Resize(LR - 1)
    .Value = .Value
End With
With Cells(2, Profit).Resize(LR - 1)
    .Value = .Value
End With
ActiveWorkbook.SaveAs "c:\" & sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
ActiveWorkbook.Close False
End Sub

 

[fisht]

Hey Dermie- thank you for your follow up, unfortunately I am getting an "unable to get the match property of the worksheetfunction class" error when running the macro.

Do you know why this is occurring? And just to help clarify (not sure if this is whats causing the error) but the code should be looking up the column headers in row 24 and between columns "A through PP".

Also, correct me if I am worng but is the code you provided me supposed to value the cost, revenue, and profit columns? If so, I am looking for the macro to do the opposite and leave the cost, revenue, and profit columns as is and value all the remaining columns in that worksheet when saving out.

Please let me know if I can help clarify anything else and thank you in advance!

this code worked when I tested it.
let me know the outcome please..

Sub fisht()
'for Sub savePSh()
Dim sh As Worksheet
Dim LR As Long
Dim Cost As Integer, Revenue As Integer, Profit As Integer
ActiveWorkbook.Sheets("P&L").Copy
Set sh = ActiveWorkbook.Sheets(1)
sh.name = sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
Cost = Application.WorksheetFunction.Match("Cost", Range("1:1"), 0)
Revenue = Application.WorksheetFunction.Match("Revenue", sh.Range("1:1"), 0)
Profit = Application.WorksheetFunction.Match("Profit", sh.Range("1:1"), 0)
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Cells(2, Cost).Resize(LR - 1)
    .Value = .Value
End With
With Cells(2, Revenue).Resize(LR - 1)
    .Value = .Value
End With
With Cells(2, Profit).Resize(LR - 1)
    .Value = .Value
End With
ActiveWorkbook.SaveAs "c:\" & sh.Range("E6") & "_" & Format(Date, "yyyy mm dd")
ActiveWorkbook.Close False
End Sub

 

[dermie_72]

Thank you for this feedback. I believe the error is that I did not closely read your initial post where it said the header is in row 24. You would need to change the Cost = Application.WorksheetFunction.Match("Cost", Range("1:1"), 0)
to Cost = Application.WorksheetFunction.Match("Cost", Range("24:24"), 0) for all matches. This should work. THe error was coming up because the string you were looking for was not in row 1, and we had selected 0 (exact match) at the end.
The problem comes about because your criteria has changed.
Initially you asked for:
" I need some help in adjusting the macro to only value out columns with specific headers in row 24"
You mentioned that the headers were:
"The specific column criteria is: "Cost", "Revenue", & "Profit"."
Now, it's the opposite.

So just to confirm before I do anything else, you want to value all of the cells from row 24 to the last row in the activesheet (or the sheet position1 in the active workbook), except where the words cost, revenue and profit appear in the headings in row 24?

 

[fisht]

Hey Dermie- you are correct with your last statement; "So just to confirm before I do anything else, you want to value all of the cells from row 24 to the last row in the activesheet (or the sheet position1 in the active workbook), except where the words cost, revenue and profit appear in the headings in row 24?"

Also, I made the adjustments you indicated (changing the row range from "1:1" to "24:24") and the code worked great (besides performing the opposite valuing of columns, which the next code change will take care of). I apologize for the confusion in my directions initially.

I really want to thank you for taking the time to thoroughly understand the issue you are trying to solve! I look forward to the next code adjustment!

Thank you for this feedback. I believe the error is that I did not closely read your initial post where it said the header is in row 24. You would need to change the Cost = Application.WorksheetFunction.Match("Cost", Range("1:1"), 0)
to Cost = Application.WorksheetFunction.Match("Cost", Range("24:24"), 0) for all matches. This should work. THe error was coming up because the string you were looking for was not in row 1, and we had selected 0 (exact match) at the end.
The problem comes about because your criteria has changed.
Initially you asked for:
" I need some help in adjusting the macro to only value out columns with specific headers in row 24"
You mentioned that the headers were:
"The specific column criteria is: "Cost", "Revenue", & "Profit"."
Now, it's the opposite.

So just to confirm before I do anything else, you want to value all of the cells from row 24 to the last row in the activesheet (or the sheet position1 in the active workbook), except where the words cost, revenue and profit appear in the headings in row 24?