Creating an Excel Template for different data size

Question

I created an excel template for a file i've done for a routine work calculation. The file takes data from the data logger and does some analysis on it and outputs one number regardless of the input size.

The problem I'm having is i have to modify the sheet to suit the number of rows, as everyday the data logger outputs a different number of rows. there are about 15 sheets in the workbook and it's annoying to have to change everyone of them everyday. What i'd like to do input the data logger csv, and boom the result gets outputted.

Is there a way through vba or not to ahieve

Answer 1

the clear answer is "It depends"

what is so specific about the 15 sheets that they can't cope with a changing number of raw data records. Are they pivot tables, are they formulae that deal with each raw data record individually?

For pivot tables, specify the source as a named range (like "SourceData" and resize the named range after raw data import - this for example can be done with a VBA - one-liner like

Range("SourceData").CurrentRegion.Name = "SourceData"

alternatively, instead of a range between two cells ($A$1:$G$12) define them as columns as they will not change ($A:$G)

It is thinkable to copy formulae in dependent sheets based on the number of source records by setting the Formula property of any Range object.

For other kind of analyses that work on individual source data rows, I guess we need more info what exactly you want to achieve.

Hope this helps

Good luck