Copy transpose formula between workbooks as text

Copper Contributor

Hi, I'm stuck with the above problem. I am copying various formulas from one worksheet to a worksheet in a different workbook. Each worksheet has the same worksheets and layouts on them.

 

To avoid the new sheet referencing data from the sheet in the original workbook, I have copied the whole sheet as formulas and pasted them into Notepad. I've then copied and pasted the text onto a blank sheet in the other workbook, and it has been working fine.

 

I now am stuck with getting SPILL errors from pasting the TRANSPOSE function as text. For example, a cell formula of:  =transpose(b1:h1)  returns a column with 7 cells with #SPILL, then 7 cells with the correct numbers. This occurs whether I used "ENTER" at the end of the formula or "CTRL-ALT-ENTER". It also occurs if I paste the text into a cell on the same spreadsheet.

 

I have a lot of worksheets to paste this into and would appreciate any help with sorting this error please.

Thanks

2 Replies
Sorry, I meant each workbook has the same worksheet labels and data layout.
I found a workaround. I created two templates: the first pasted formulas that excluded the transpose commands, then the second added them. But the cells with a transpose formula had to be in the last row of the template, otherwise they could cause spill errors.