If I’ve got a spreadsheet with dynamic elements – things calculated from elsewhere, tables with filters applied etc., can I save the sheet out in a non-dynamic format?

In other words, I’d like it to behave as if I’d copied the tables into notepad and then back again, so the results are there without the underlying filters and calculations.

paste special formats then paste special values

Highlight the cells you want to copy, open up another sheet/workbook, right click on blank cell, select ‘Paste Special’, then click ‘Values’, click ok.

Time for a bit of VBA / Macro recording then by the sounds of it.

Any sly way of incrementing the filter at the same time?

Mmm? Confused now? I thought you just wanted the ‘data’ which is generated as a result of the calculations & filters – without the calculations & filters actually being there?

But if you still want some sort of filtering then you would need to reapply these filters to the copied data.

Don’t see why you’d need record VBA/Macro, if you just wanted ‘non-dynamic’ data.

have you tried breaking links? Alt>Edit>Links>Break links.

if your trying to do it automatically record a MAcro of you doing what you want, modify, and then attach to a button to run,

I do just want the data – what I’m doing is trying to produce a form for a (numbered) recipient to fill in.

In my head, it would run something like this: start everything off with the list filtered for recipient 1, de-dynamise the data by whatever method, then save it as a new excel workbook, preferably with a name along the lines of “recipient number, recipient name.xlsx” (hence the need for a bit of VBA). In an ideal world, the macro would then increment the filter to pull everything for recipient 2, and go through the process again, until everything is done.

Obviously incrementing a counter using loops is fairly simple, but can I set a filter from within VBA?

(And yes, I suspect this is something better done in Access / mail merge, but it’s not on the cards).

To save it without all functions, then Save As .csv

righ, i think i have what you are trying to do. I had to create a order acknowledgement form, the way i did it was two spreadsheets. Spreadsheet 1 is the data entry form, spreadsheet two the order record form. You would enter details in the first spreadsheet, then click a button at the end. The button would run a macro that copied the required cells on to the second spreadsheet. The second sheet also assigned a serial number to each record.

It might make more sense in Access, but Access is crap with reports IMO.