You said: I have since it has worked for years... I have never used excel macros before last year.
OK

Imagine an old network, from the '90s and 00's, one built by several different IT companies. One that used a Token Ring system, for a small office with several Computers using different Operating Systems. And they want you to upgrade the system because they suddenly have 100 Desks all running several newer OSes.

Even though you could keep using Token Rings and just add some hardware to the existing system, would you?

That's what I feel like when I look at your current VBA.



Please think of us here at VBA Express as VBA Software Engineers. We have a specific lingo that you have been missing.


  • Raw Data is straight from your distributors, via Email, CSV, Google Files, or the ERP.
  • Processed Data is the output of the ERP.
  • Imported Data is what is on an Excel Sheet.


It may be that one or more are the same, but, be specific. As it is, We Don't have a clue since you have said that the Excel sheets are Raw Data. As long as you show us the earliest version you have regular access to.

At my current understanding of your requirements, I see One Macro Enabled Workbook with 6-7 Sheets
  • Price References
  • Product Code References
  • Product Names References
  • Two Templates
  • One or two Column Cross Reference Sheets


The Column Cross References will be used to cross the various columns of the Imported Data to the Templates/
All this is to make it easy to add new Distributors and maintain price lists, Product name replacements and Business names reference in one place.

It is also essential to creating fast and efficient code. Constantly referencing Worksheet Ranges in different Workbooks is as slow and error prone as Token Rings and Routers in a network. Any good code will be using Arrays for all such references