Indirect formulas turns to #ref! when emailed as values

rbrace

New Member
Joined
Apr 30, 2012
Messages
17
My workbook contains 78 project worksheets. I have a summery page I am trying to email using Rons Mail one sheet script: http://www.rondebruin.nl/win/s1/outlook/amail2.htm

The past as values has been uncommitted in the code.

Code:
[COLOR=black]'Change all cells in the worksheet to values if you want[/COLOR][COLOR=black] 
       With Destwb.Sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With 
[/COLOR][COLOR=black]    Application.CutCopyMode = False
[/COLOR]


It works for everything except the Indirect formulas end up #ref !.

My Summery Page Layout:
Column A67:A145 contains a list of worksheets.
Column B67:T145 contains INDIRECT($A67&"!AC$49") to pull data from the worksheets. The AC49 changes for every column.


Should I use a different formula to pull the data into my summery page, or a different email script. The workbook is used by multiple people across the country some with limited computer skills. I'm looking for a one click email active worksheet as values option.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top