Creating a macro compatible with several similar but not identical spreadsheets

[mmahercpa]

Hello,

For my job, I work on audits of various companies. Several of the companies are construction companies with similar-looking financial statements. I have the spreadsheets for each company with ten years of financial data and analytical formulas that contains graphs linked to the data for the last five years. I want to create a macro that will add a new tenth year column and take out the current first year column to maintain ten years of data, but I need to maintain five years of data for the graphs. I have been able to do this for one company through the macro recorder and some code manipulation in VBA (I am still a beginner at macros), but the macro is not fully compatible with the other companies due to slight differences in line items in the data or formulas. Does anyone have any ideas of how to generalize the macro to work for each company without writing a totally new code for each one?

Thank you

p.s. The code is very long and complex, so I will only include portions of it if necessary.

 

[Smitty]

Welcome to the Board!

Are the column headers consistent? If so, then you can use Application.Match("Header Name", Range("1:1"),0) to find the header you need to delete regardless of where it is.

E.G.

x = Application.Match("Header Name", Range("1:1"),0)
Columns(x).EntireColumn.Delete

HTH,

 

[mmahercpa]

Thank you Smitty,

Yes, the columns for each company are exactly the same. From left to right, every company appears identical. It is from top to bottom that the differences appear. For example, some companies have inventory, and some do not. That lends itself to a new row for those companies that do have it. Since some will have inventory, they will have various inventory ratios that the other companies will not have. However, there a few "average" ratios that require data from the year before to compute correctly. Because of those, I have those values copied and pasted special as values only. But I don't want that happening with all of the formulas, just the three or four that are "average" ratios.

Thank you

 

[mmahercpa]

I am using Windows 7 and Excel 2013, if that helps anyone.
Also, I cannot figure out how to paste a screenshot of the spreadsheet (or a portion thereof).

Thanks,

mmahercpa