VBA - Multiple Templates w/ One Worksheet - Adjust formats/formulas or hide unused columns?

[AcDel]

We have a large workbook in the office and we use it as a template for clients.

We have multiple worksheets that are very similar, but the main differences are the number of columns and sizes. I'd like to consolidate this with VBA so we are using 1 worksheet that can be adjusted to the proper template with VBA, rather than working with 5 different worksheets.

Would it be better to:

• Have all the different already set up formats in the worksheet and hide the unused columns?
  • For example. Columns B:J would be one template, K:N would be another template, O:V would be another, etc.
• OR would it be better to have VBA adjust all the necessary formatting?
  • This would include deleting cells, changing column sizes, removing/adding formulas

I'm trying to think of terms of performance and stability as both methods can be used to achieve the same result.

I hope that makes sense. Thanks in advance

 

[Derek73]

Numerous different directions you could go. Depends on what this is supposed to accomplish. What's the "why?" From what I can gather, it's to reduce visible clutter?

I'd create 5 template sheets, then hide them. Create an "Add a client" worksheet with a selection and a button to trigger the macro to copy the correct template to a new worksheet to make it easy to use.

 

[AcDel]

Numerous different directions you could go. Depends on what this is supposed to accomplish. What's the "why?" From what I can gather, it's to reduce visible clutter?

The reason is to reduce clutter and to have a long IFERROR code function properly, which references all the template sheets. Our process now is we have 10 templates in the workbook and the user hides the other 9 that will not be used for that particular client.

I'd create 5 template sheets, then hide them. Create an "Add a client" worksheet with a selection and a button to trigger the macro to copy the correct template to a new worksheet to make it easy to use.

That is essentially what we do now, but the issue I'm running into is I have a long IFERROR code that references these sheets. If I have to create a new sheet, then the new sheets won't be included in the formula and could defeat the purpose of it.

Right now the whole workbook has 120+ worksheets, so I'm really just trying to consolidate it as a whole and minimize the possibility for errors.

I appreciate your help with this.