Formatting data

[rn]

Hi

When formatting the same type of data frequently, I
design a template with three sheets, the first to paste
the raw data, the second a temporary for the
fuctions/formulas for fomatting the data in the former
sheet and the last sheet is to paste the temporary sheet
data as values. Please see example below.

In the second sheet I write the formulas for more than
the rows of expected data. Say somtimes 200 rows as the
rows of data varies from 10 to 150.

My questions are,

1. Is this the most efficient way of doing this task?
If not what is a better solution.

2. How can I adapt my function/formula sheet to only
write fuction/formulas only for the number of rows in the
data sheet?


Data sheet

ID CustName ProductCode
12 Joe Bloggs FEN

Temporary sheet

ID ProductName
12 Fulltime English (This name is derived from the
ProductCode in the Data Sheet and a lookup table using
the Vllookup function)

Final sheet
Just copies the Temporary sheet as values using macros.

Thanks.

 

[K Dales]

Like many things in the world of computing there are many paths to reach the
same goal, all with different advantages/disadvantages; you could do all of
this by hard-coding the formatting and formulas, if you wanted to, and
perhaps there might be some benefit to that (smaller file size? Faster
execution?) - but "if it ain't broke, don't fix it!" It seems to me your
approach is much easier to set up, less prone to "bugs", and a lot easier to
maintain (for example, you can easily change formatting without having to
work out the code). I think sometimes we programmers (I include myself here)
get so hung up on coding a solution we ignore the possibility of some
creative solutions using only standard Excel features.

And you can enhance the solution with code as needed, which addresses your
second question. Again, there are many approaches. You could step through
your data row by row, into the formula/formats range on your temp sheet, then
copy and paste formats and values to the result sheet; or you could duplicate
your formatting and formulas on the temp sheet to match the number of rows on
the original sheet and do it all in one operation after that. In fact, you
could apply the formulas and formatting to your entire temp sheet and then
only copy/paste the number of rows you need: assuming you have set up the
temp sheet this way, it would just be something like this:

Sheets("MainSheet").Range("DataRange").Copy
Sheets("TempSheet").Range("A1").PasteSpecial xlPasteValues
Sheets("TempSheet").Range("ResultRange").Copy
Sheets("FinalSheet").Range("A1").PasteSpecial xlPasteValues
Sheets("FinalSheet").Range("A1").PasteSpecial xlPasteFormats

And, if you need to find the range, assuming you have a continuous list and
you know where it starts (but not necessarily where it ends) you can use the
CurrentRegion; for example, if the formulas and formats you are using on your
temp sheet start in cell A1, and form a rectangular region with no blank rows
or columns, you can use Sheets("TempSheet").Range("A1").CurrentRegion.Copy to
copy the whole list at once.

Don't know if this will all work for your spreadsheet, but a few ideas to
think about at least...