Remove external link in the calculations when pasting sheet from

Resolved · Urgent Priority · Version 2016

Tue 8 Aug 2017, 20:38 Delegate Yilin said...

Yilin has attended:
Excel VBA Introduction course

Remove external link in the calculations when pasting sheet from

I am using VBA to paste the whole sheet from one workbook to another. There are external links directed to the source workbook in formulas like Address() and Sumif(). I want to remove these external links when I paste in.

Wed 9 Aug 2017, 10:41 Trainer Jens said...

RE: Remove external link in the calculations when pasting sheet

Hi Yilin,

Thank you for the forum question and thank you for the nice feedback.

I have created to workbooks; One where I am using the combination Address + Index function similar to your workbook and a destination workbook.

With only the destination workbook open I started the macro recorder. I recorded that I opened the source workbook copied the functions and some additional columns and pasted the data in the destination workbook.

I ran the macro and everything went fine. I didn't get the unwanted references.

Yesterday I believed that the Address function was the problem, but it is not.

I will need to have a closer look at your file to find out the problem.

You can send it to:

info@stl-training.co.uk

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 9 Aug 2017, 11:37 Delegate Yilin said...

RE: Remove external link in the calculations when pasting sheet

Hi Jens,

Thank you for looking at it. I am not able to send them over as they are from client. I managed to solve this problem by changing the link into current workbook with VBA.

Wed 9 Aug 2017, 11:45 Trainer Jens said...

RE: Remove external link in the calculations when pasting sheet

Hi Yilin,

I am happy that you found a solution.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 16 Aug 2017: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Hide data in Excel Worksheets

Let's say you have some data in cell 'C5' you would like to hide from the casual viewer.

Click cell 'C5' to select it.

Click the 'Format' menu, select 'Cells'. When the 'Format Cells' dialogue box opens, click the 'Numbers' tab (if necessary), then select 'Custom' from the 'Category' list.

Double-click the 'Type' entry box and type three semi-colons: ";;;"

Click 'OK' to close the dialogue box and accept the new formatting.

The data in cell 'C5' disappears. It's still there and will work in calculations, but it isn't visible.

If you need to check the data, just click the blank cell and the contents appear in the 'Formula bar'.

View all Excel hints and tips