I inherited an Excel workbook from a colleague which left the company. Somehow, this colleague managed to hide or delete all columns to the right of a sheet. For instance, currently this worksheet ends at column BG — it's not that column BH is empty, it is not shown at all. Whether it has been hidden or deleted, I have no idea. Said colleague can't be reached to ask how this was done, and even if reached, probably wouldn't remember anyway.
Periodically I must add two columns to the right of this sheet, copying these columns from the last two, so that the cells referenced by the formulas are also shifted. For instance, suppose I currently have some in BF6 a formula like "=AnotherSheet!C6", and in BG6 a formula like "=AnotherSheet!D6", where AnotherSheet is some other sheet in my workbook. What I need to do is create columns BH and BI and populate them with a copy of the contents of BF and BG, respectively. Returning to the example, BH6 should contain the formula "=AnotherSheet!E6" and BI6 should contain the formula "=AnotherSheet!F6".
Now, if BH and BI weren't hidden, or deleted, or whatever, I could just select BF and BG, then copy and paste it into BH and BI. Formulas would be shifted exactly in the way I need them. Since I can't do that, I have to insert new columns. However, columns are always inserted to the left of the current selection (I haven't found a way of inserting them to the right). Hence I end up with "=AnotherSheet!A6" in BF6, "=AnotherSheet!B6" in BG6, "=AnotherSheet!C6" in BH6 and "=AnotherSheet!D6"; i.e. the original contents of BF6 and BG6 are carried over without change to BH6 and BI6, and since new cells are inserted to the left, formula contents are shifted left as well.
Can anyone suggest a way to either:
- recreate or unhide all columns to the right that were deleted or hidden?
- insert columns to the right of the current selection?
- some other way to quickly create 2 columns to the right that are a copy (with formulas correspondingly shifted) of the current 2 columns?
If it's not asking too much, no VBA solutions please. And in case it makes a difference, this is on MS Excel 2011 for Mac.