MACRO HELP! Save out worksheet to a new workbook and value out everything except for specific cell ranges

[fisht]

Hi All,

I am in desperate need of a macro that can save out a particular worksheet from the current workbook to a new workbook. In addition to saving out this worksheet to its own new workbook, I would like the macro to also value out (remove formulas from) everything except for a specific cell range and columns with specific headers.

Details are below:

From old workbook..
Worksheet name: P&L

New workbook..
Worksheet name- same as above (P&L)
Cell range to NOT value out- E11:E13
10 Columns to NOT value out based on headers in row 24 of worksheet- Placed Revenue, Gross Revenue, Net Revenue, Return COGS, COGS, Returns, Total COGS, Gross Profit, MMU%, GM%.
*The reason I would like these column headers to be referenced is because the positioning of these headers change to different columns.

New worksheet's file path (generic)- C:\My Documents\Financials

Having such a macro would be a HUGE help and time savings for me and I would be so grateful. Please let me know if I can assist by answering any questions. I will be continually viewing this post and will be quick to respond.

Thank you!

 

[fisht]

My previous two posts have made my request a little more complicated then it should be. I moved the button which activates the macro to the 'P&L' tab and the above code works exactly as it should. In doing so, that removed the need for the first request in my 6:31PM post.

To simplify things, the only addition to the code I need would be to not value out specific columns starting with 'BM' and onwards (ie BM, BN, BO, BP, etc.)

Thanks for your patience and help.

 

[HalfAce]

Hi fisht,
Haven't had a lot of time to go through this in much depth today, but if I understand, here are a couple things you might want to try.
As for putting your button on another sheet, you should be able to put it on any sheet you want and make this small change to one line:
Change this line from:
LstCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
to:
LstCol = Sheets("P&L").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
(If that doesn't work for some reason I'm overlooking, then you could just have your code select sheet P&L at the beginning and continue on as if that was the active sheet when the button got used.)

As for the stopping of converting to values at column BM and beyond, you should be able to just insert something like the line in blue between these two other lines in your code:
LstCol = Sheets("P&L").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If LstCol > 64 Then LstCol = 64
Sheets(Array("P&L", "Data", "MTD", "YTD")).Copy

(Column BM is the 65th column from the left, so if there are more than 64 columns in use when LstCol gets defined, you're just re-defining it to stop at column 64.)

Do either of these help?

 

[fisht]

Yes, they both were very helpful and thank you for the explanations with each.


You're the man HalfAce!

Hi fisht,
Haven't had a lot of time to go through this in much depth today, but if I understand, here are a couple things you might want to try.
As for putting your button on another sheet, you should be able to put it on any sheet you want and make this small change to one line:
Change this line from:
LstCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
to:
LstCol = Sheets("P&L").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
(If that doesn't work for some reason I'm overlooking, then you could just have your code select sheet P&L at the beginning and continue on as if that was the active sheet when the button got used.)

As for the stopping of converting to values at column BM and beyond, you should be able to just insert something like the line in blue between these two other lines in your code:
LstCol = Sheets("P&L").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If LstCol > 64 Then LstCol = 64
Sheets(Array("P&L", "Data", "MTD", "YTD")).Copy

(Column BM is the 65th column from the left, so if there are more than 64 columns in use when LstCol gets defined, you're just re-defining it to stop at column 64.)

Do either of these help?

 

[fisht]

Hi HalfAce,

I would like to make another adjustment to the code but don't know how to. I am seeking your help with this! I would like the finalized code to do everything the same except that the new file should be saved out in a CSV format. Is this be a complex change to the code?

Thank you!