Copy and Paste help?!

[ManCityPete]

Apologies but this is actually a copy and paste question!

I am trying to update a spreadsheet inherited from a colleague. It's similar to a training matrix.

Each tab has a table containing a person's name on the row and a list of training courses along the columns.

For each course, there is a date that they did the training in one column, with a formula that determines the date based validity of the course in the next column. There could be up to 70 courses (60 columns) on each tab. There are upwards of 50 tabs. That's effectively over 3,000 columns of dates.

I have a new formula that I want to use next to each date.

Whilst it is very easy to select a column of formulae, copy it and paste it to the next formula column, there are over 3,000 columns.

With my knowledge, I can't copy and paste the who table because each table has dates specific to that course and that trainee.

Is there any way that I can copy the whole table and only paste the columns with formula in, so that the dates are not overwritten?

I know that I can copy alternate columns but when this is paste, they paste together, not in alternate columns.

Or is there a cleverer way to achieve what I want?

Dumb question, I can hear you saying already!!

 

[JackDanIce]

HI Pete,

What version of Excel are you using? You could automate this using VBA or if using a later version of Excel, PowerQuery

Effectively, you've inherited a database from your colleague, built in Excel. To make that many column changes, automation via VBA or PQ is only viable solution.

Without the workbook, it'll be difficult to explain "How to do this" I'm afraid

Can you post an example of one sheet? You can find instructions on how to use XL2BB - Excel Range to BBCode to do this?

Limit the number of columns to 9 and show a 'before' and 'after'

 

[Radoslaw Poprawski]

were playing guessing game here,
provide minisheet, please.

maybe F4 will solve ur iissue.
F4simply locks a range

you can fully lock cell / range (double $ sign on a range)
you can lock only columns ($ sign b4 a letter)
you can lock only rows ($ sign after the letter but b4 the numeric value)

now if you copy formula with mix-locked cell references properly it should calculate properly.