Here's how I typically structure the Excel VBA code for client exports:
turn off screen flickering so the user doesn't see the screen fly around while the macro runs
run error checks to make sure the required tabs, cells, etc. actually exist
copy out the required tabs into a new file- I never have the macro actually touch the underlying pricing tool
loop over the new file to unhide and ungroup rows and columns, copy / paste values over all formulas, autofit rows, delete all conditional formatting, shapes, comments, notes, and named ranges
delete all unneeded columns
filter out and delete all unneeded rows (e.g., zero-value line items)
reset the view to the top left of each tab
Comments