Sharing Some Non-Trivial Formulas Across Excel Documents

Question

I've created some reports using a CSV file generated from a database in Excel. I've added several columns using formulas that reference other columns. I've put a good bit of work into creating the report.

I now would like to apply the same formulas that I've added to this spreadsheet to another CSV file that is very similar to the original CSV - perhaps some new columns added.

I'd like to apply the same formulas without manually recreating all of them and the additional columns. I can't figure out an efficient way to do this - I always end up retyping and creating the formulas and columns again manually.

Is there a better way to do this?

Answer 1

This kind of thing was the way I first got into VBA. Try recording a macro of you adding the columns & formulas - at the simplest level this will allow you to replay the macro to apply the same changes to another CSV, solving your original problem.

With a bit of studying, you could also take it to the next level and optimise the code to strip out some of the redundancy that the macro recorder produces.

If this piques your interest, and you have to carry out operations like this regularly, then examining this code and digging a bit deeper into the object model will open your eyes to a whole new way of working with Excel, not to mention saving you (and your colleagues!) many hours in the process.

Answer 2

Here is a two step process that might work.

1. Copy the formula sheet or the entire workbook to a new XLS.
2. Copy and paste the data sheets or columns from the second XLS to this copy in the correct places
3. You previous computations should work on new data in this copy

Answer 3

Try Copy & Paste except when you go to paste, hit "Edit" -> "Paste Special" in the menu. It will show you a dialog with a couple different ways you can paste what's in the clipboard.