"Paste Link" remains gray and not accessible

[Corried]

Hello Excel world,

I have a problem...
I am trying to copy a table and link the table by pasting it into another workbook. But he" paste link" is gray out.

What do you suggest I do to fix the problem? Please view the video below...

Thanks in advance

Cheers.

https://drive.google.com/file/d/16TupRd ... sp = sharing

 

[mamady]

Hi Dear,

I can think of three possibilities to your issue:

A) The destination sheet is probably protected. Go to Tools >>Protection...Unprotect Worksheet

B) the Excel toolbar has an issue, needs a reset
Rightclick on an empty spot on any of the toolbars.
Choose Customize (at the bottom)
Toolbars tab
Select worksheet menu bar
and click reset
(Note: You will lose any customizations you've made to that toolbar, though. )

C) Alternatively, try the following workaround solution
1. In the destination cell type =
(Do not press enter or sec)
2. Change to the the other workbook by using the 'window' menu or using ctrl+tab
3. Select the cell you want to link
4. Press enter

Or type directly:
=[Filename]Sheet1!A1

Replace Filename, Sheet1 and A1 as needed

Regards
M. Yusuf

 

[Corried]

Mamady. A pleasant good day to you. I real appreciate your feed back and your suggestions.

The second option have work. the only problem I am having is, not pasting as a table. Every month the 1st work book table is updated automatically. And I am getting some issues of the "Paste link" grabbing the data. What do you suggest I do to capture the data in a table format?

Thanks in advance.

 

[mamady]

Hi Corried,

If the source of the information (i.e. input data) is fixated (i.e. from the same sheet, same column and row numbers, you can use a formula to feed the other sheet or a macro

I will be happy to help you with that if you share a sample sheet.

Regards
M. Yusuf

 

[jkpieterse]

Check out Data, Get Data, From File, From Workbook.

 

[Corried]

Thank very much "Mamady"

The 2nd Link (GDP_Annual_Growth_Rate_%). Is to copy the data "Table" from United State Tab and "Paste Link" the same "table" into (Initial_Jobless_Claims) Workbook in United State Tab.
My problem is: Anytime a new row is added to GDP_Annual_Growth_Rate_% workbook, United State Tab. It should mirror to the next workbook.

If this problem is successful. I would like to duplicate the same process and mirror the data into the other tabs in Initial_Jobless_Claims Workbook.

Thanks in advance

Corrie

Here are the links below:

Initial_Jobless_Claims.xlsm

drive.google.com

GDP_Annual_Growth_Rate_%.xlsm

drive.google.com

 

[mamady]

Sorry for the late response,

Thanks for sharing this example

The best way in opinion for your requirement is to do the following:
1. Go to (Initial_Jobless_Claims.xlsm) workbook
2. From the ribbon, select Data >> Get Data >> From File >> From Workbook and select (GDP_Annual_Growth_Rate_%.xlsm) workbook, click import
3. From the Navigator, scroll down and select United_States178, then from the drop-list choose Load To
4. Select the location, and the type "Table" (you may need to create a new sheet as it will not allow you to overlap and existing table/range)
5. whenever a new data is added to the relevant table in (GDP_Annual_Growth_Rate_%.xlsm), all that you need to do in (Initial_Jobless_Claims.xlsm) is to do Refresh All (Ctrl+Alt+F5) and the new added rows and data will be reflected in the destination table.

You can check the attached example I created in Sheet 1, with the new dummy row I added in the last. Please test it yourself.
I changed the name of (GDP_Annual_Growth_Rate_%.xlsm) to (GDP_Annual_Growth_Rate_% test.xlsm), so that it won't conflict from the file you already have on your drive.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

Important notes:
- Refresh data will not work if the changes made to the source file is not saved
- If the source file name changed, the connection will break. you can either recreate the connection of amending it from Query tab

Check the steps and let me know, please.

Best Regards
M. Yusuf

 

[Corried]

Sorry for the late response,

Thanks for sharing this example

The best way in opinion for your requirement is to do the following:
1. Go to (Initial_Jobless_Claims.xlsm) workbook
2. From the ribbon, select Data >> Get Data >> From File >> From Workbook and select (GDP_Annual_Growth_Rate_%.xlsm) workbook, click import
3. From the Navigator, scroll down and select United_States178, then from the drop-list choose Load To
4. Select the location, and the type "Table" (you may need to create a new sheet as it will not allow you to overlap and existing table/range)
5. whenever a new data is added to the relevant table in (GDP_Annual_Growth_Rate_%.xlsm), all that you need to do in (Initial_Jobless_Claims.xlsm) is to do Refresh All (Ctrl+Alt+F5) and the new added rows and data will be reflected in the destination table.

You can check the attached example I created in Sheet 1, with the new dummy row I added in the last. Please test it yourself.
I changed the name of (GDP_Annual_Growth_Rate_%.xlsm) to (GDP_Annual_Growth_Rate_% test.xlsm), so that it won't conflict from the file you already have on your drive.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

Important notes:
- Refresh data will not work if the changes made to the source file is not saved
- If the source file name changed, the connection will break. you can either recreate the connection of amending it from Query tab

Check the steps and let me know, please.

Best Regards
M. Yusuf

Mamady. I followed your direction step by step and it works wonderfully. I have managed to paste the data on the same worksheet, change some data and refresh query and it work...

My plan is to link "GDP" values with each country which represent a worksheet.

However, In some workbook, I have over 180 worksheet. If I plan to make a a query for each one, what would happen when it refresher query al at the same time?
I am not sure, I am afraid query may break or my computer may become unresponsive.

What is your views?

Best Regards

Corrie

Thank you For Your Support And Your Passion To Help Others...

 

[jkpieterse]

If it is ok for those 180 Worksheets to load into one table that is also possible.

 

[Corried]

If it is ok for those 180 Worksheets to load into one table that is also possible.

I was waiting on an answer to move forward. Thanks jkpieterse.

If so... How am I going to do that?

Thanks in advance