RE: VBA Code to hide rows, if column value is a certain value, and then export to new workbook

[tyrese215]

RE: VBA Code to hide rows, if column value is a certain value, and then export to new workbook

Hi,

In a sheet/tab named “Reporting_Interface” I have a drop down box that has a list of the 5 tab names within my workbook.

Each of these 5 tabs have different data displayed in a very similar format.

I need a magic button created below this drop down box that says: “Generate”

This button will be driven by VBA code that will do all of the following when clicked for the Tab name selected in the drop down box:

1. Code will first Unhide all rows, then if column W has a value of “hide”, then code will auto hide Entire row in that selected tab name.

If columns are hidden in the tab, then keep these columns hidden in the output. No need to unhide and hide Columns like we are doing to rows.

** To make this export a fast process, please programmatically turn “calculations” off during this unhide and hide process. After hiding rows, you can switch recalculate back on, just before exporting the tab as detailed below.

1. Code will then prompt user which folder they would like to save the tab in. Default directory path should be the same directory path where the current workbook is saved. User can then navigate or create a new folder where they would like the file saved. (If you can’t read the default path where the current workbook is saved, then an alternative could be that user can place the current path in cell D5 of “Reporting_Interface” tab. If this path does not exist, path will default to the root)

1. In cell D5 of “Reporting_Interface” tab I have the cell value of “XXXX”. User can change this value to whatever they wish. When file is exported, file name will be the value in: D5, followed by tab name, followed by date stamp, followed by timestamp. In this format:

Where: d5 is the cell value
Where: TTT is the tab name
Where: DD-MMM-YYYY is the current date of day, month and year
Where: Time is the current time of extract

1. The tab that will be extracted has many formulas and formats, and some small images in the tab. It is extremely important that the Entire tab is exported and saved is the Exact same formatting, However it is Very importantly the exported file should ONLY be values and No formulas whatsoever. There should not be any “Edit Links” option at all because file should be completely independent. The exported file should also have the picture intact as well, and sheet should be protected, with No password.

1. When new workbook has been successfully exported and saved, user should be prompted with a message saying:

Hi there,

D5_TTT_DD-MMM-YYYY_Time has successfully been exported and saved into this destination folder:

xxx/xxx/xxx

Thank you…

(Where xxx above is the path selected.)

1. After export, the original workbook and the tab selected in the “Reporting_Interface”, should default back to All rows in that tab to be Unhidden. Just rows to be unhidden. If columns are hidden then keep them hidden.

1. All the above should happen without the screen flickering or changing tabs (like what happens sometimes when recording a macro). User should only see the current “Reporting_Interface” tab and code should execute in the background.

Summary: End result would be the tab selected in the drop down box would be exported when button is clicked. Exported file will have all rows first unhidden (to reveal everything incase row values have changed), then entire row should be hidden if column W has the value of “hide” in it. Next, entire tab will be Auto Named and saved using the naming convention in step 3 and saved in the folder in step 2 above. All rows in the Original tab to be exported should be unhidden.

Thank you