Formatting Macro

Hello,

I posted this in Commercial Services but did not get a response.

I am somewhat new to macros and have one I thought would be simple. I tried recording the macro but quickly found myself out of my depth.

I have multiple sheets with data in table form. Each sheet has the same exact headers (headers are in the range A10:AE10) but the number of rows vary.

The formats for each column vary from General, Accounting, Number, Date etc, and may change in the future.

I am in need of a macro that can go into specific sheets and reformat the range of data in the columns based on the format of a specific range of cells in another sheet.

I have a master range, on a sheet I named “Master”. In cells A1:AE1, Here is where I have set my formats. (I figured it would be easier to change the formats here if things change)

The macro will apply only to the tabs that start with a "T" or "J" (The J sheets are the "Job ####" sheets of the attached)

It will then go to first sheet, find the range of data below the header (starting in A11 and going to AE?? since number of rows will vary), and format the data based on the formatting of the range from the Master tab (cell A1 format from the Master will equal the format for column A in the sheet)
It would then apply a thin, solid border around the whole range and “dotted” borders within the range. (I am not sure how to describe the varying levels of borders, but it is the one directly below “None” on border selection).

It would also have the ability to hide columns, say H, L, & P columns for this example.

Once complete, it would move to the next sheet until all sheets that start with a J or T are formatted.

There is another macro in the workbook called JobCodeOutput that the macro may be able to fit into. I kept my request as a separate macro not to confuse the issue.

Any suggestions?

Hi Norie, Thank you for such a quick response! The macro works as it should. Thank you. However there are some other externalities that I realized I need to take care of. The other macro I run before this one, puts lines below the range this formatting macro selects, so those lines end up staying. Is there a way to clear the lines (or formatting) below the header, before this macro puts in the new formatting? Also is there a way to have the macro adjust column width to the same as in "Master"?

Again, thank you for your feedback. It is greatly appreciated.

Its the way the JobCodeOutput macro is set up. It basically creates new sheets based on values in the RollUp tab. Because it creates new sheets, I need to format the new sheets, therefore the formatting macro needs to run last. I did not create the JobCodeOutput and my attempts to fix the issue are a bit beyond my knowledge. If there is a way so the JobCodeOutput will create new sheets without the formatting, that could work to.

Sorry, let me back up.
Are you able (or willing) to open the workbook? It could explain the issue a bit better, but I understand if you will not. I'll try to summarize below

The JobCodeOutput macro, (which is in the attached workbook) looks at the RollUp tab, finds all unique values in column E, and then creates a new worksheet based on that unique value and returns all the rows with that value. The new worksheet is named "Job xxxx", where "xxxx" is unique value. In this example spreadsheet the JobCodeOutput creates about 20 new worksheets. When it goes to find the unique values and make a new sheet, the lines comes back weird. Sometimes there will only be 1 or 2 rows for the "xxxx" number but it creates lines for 20 rows.
My end goal is to get a "clean" sheet for printing purposes. It does not look very good with formatted lines below the actual data range. I am hoping the formatting macro can clean up the issue.

Again, Thank you!

Yes you are right. the headers are in row 18 and the data starts in 19. To run the job code macro, first delete all the tabs named Job xxxx. the macro creates these and if the already exist, you get an error.

No worries about the time. I greatly appreciate the effort.

Hi Norie,
I played with the macro and I think I was able to remove the formatting from the JobCodeOutput macro and the formatting macro runs just fine. The only thing that would help if there is a way to have the macro copy the column widths in the master. Is this an easy insert? Thank you again.

Thank you Norie!