<div>
Hi,<br />
&nbsp;<br />
Try this.<br />
&nbsp;<br />
1. Select A1:K21 (in Original file) and assign it a name, say Table1<br />
3. Select A1:K21 and press Ctrl+T to convert to a Table and save the file<br />
4. Click on any cell in Remote.xlsx and go to Data &gt;&nbsp;From Other Sources &gt;&nbsp;From Microsoft Query &gt;&nbsp;Excel Files<br />
5. Navigate to the folder where the file is saved, select the file and click on Next<br />
6. click on the plus sign in the left hand side box, select the column which you want on Remote.xlsx and click on the &gt;&nbsp;Symbol<br />
7. Click on Next twice<br />
8. On the last screen, select &quot;Return Data to MS Excel&quot;<br />
9. In the Import Data box, select Table and click on Finish<br />
&nbsp;<br />
Now as and when you add/delete rows from original.xlsx, just right click on any cell in the data area in remote.xlsx and select Refresh.<br />
&nbsp;<br />
Hope this helps.
</div>


<div>
Perform same operations for other tables also.<br />
<br />
Let me know if any help needed on to this...
</div>


<div>
Ctrl+T will only work when you are in editable mode (means excel workbook should not be in read only status)
</div>


<div>
Sorry the attched work book is not in .xlsm &nbsp;which is the format my original is in... It has a ton of macros already.<br />
In the attached workbook you can see that they are all are tables already.<br />
This is a copy paste from another solution I have already tried this before I posted the question.<br />
<br />
Unexpected file format. Does not except .xlsx or .xlsm format, only .xls<br />
<br />
Any ideas?<br />
Thanks<br />
Chris
</div>


<div>
Sorry, It was typing mistake.<br />
<br />
Consider the extension as .xls<br />
<br />
It's the standard solution.
</div>


<div>
What do you mean by remote?
</div>


<div>
Roy its just a second workbook.
</div>


<div>
Original workbook is password protected.<br />
<br />
Why do you need two workbooks?<br />
<br />
You could use VBA to update the Tables when the remote workbook is opened
</div>


<div>
Roy, <br />
Thanks for the question.<br />
The main workbook has 30 sheets with private employee information. That's why it is password protected. Managers can open it but not change anything. They enter a password to view only, and only me and the HR/Payroll have the second password to write to the workbook. I used the basic, &quot;save as&quot; then &quot;tools&quot; then &quot;general&quot; then password to open.<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/09_w40/1119670/05.JPG" target="_blank" title="05.JPG (13 KB)"><img alt="05.JPG" class="file-block lazyload" style="max-width: 336px;" data-src="https://filedb.experts-exchange.com/incoming/2016/09_w40/1119670/05.JPG" /></a><br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/09_w40/1119671/06.JPG" target="_blank" title="06.JPG (24 KB)"><img alt="06.JPG" class="file-block lazyload" style="max-width: 600px;" data-src="https://filedb.experts-exchange.com/incoming/2016/09_w40/1119671/06.JPG" /></a><br />
I want to use just the employee names (just the one sheet) which changes a lot.<br />
<br />
I want to link the tables so I don't have to keep updating too many other workbooks. I have a few more workbooks that I have to update all the time, every time an employee changes department or there is a new employee hired. So this will help me fix those other ones too.<br />
<br />
Does that kind of make sense?<br />
<br />
Thanks so much
</div>


05.JPG

06.JPG

original.xlsx

<div>
Hi Roy,<br />
Yah the 30 Sheets are all different. It is a calendar program/vacation program/ vacation request program. It has vacation request forms that are linked to the main list of names, keeps track of allocated vacation days for the year separated by department. It has a dashboard that allows the Payroll guy to enter employee name and day requested for vacation, and sends his name to the calendar month and his hours to a chart for one of three different types of vacation days. Everything is linked. <br />
<br />
I wanted each department to be separated visually. So I have one table for each department. It all works great. I had a ton of help with all the parts individually, and put them all together.<br />
<br />
The problem is like this now... I need to pull all the names from all the departments into one list of employees which gets used for 3 or 4 other programs, but when every time the PRIMARY list of names (by department) gets updated I have to update the names in 4 other places.<br />
<br />
If I change the Calendar program now, it will mess EVERYTHING up in that system, so I am kinda stuck using a table for each department.<br />
<br />
Alternatively if there was a way to pull names from each table and move the names into one big table that I could later sort, that could also work.<br />
<br />
Thoughts??<br />
Thanks Roy<br />
Regards<br />
Chris
</div>


<div>
Thoughts:<br />
<br />
Maybe you should be using Access. You can link Access Tables to Excel and they will update in the Access Table<br />
<br />
From the Table that I uploaded I could easily write code to move &nbsp;all a department's employee's to another sheet for further work.<br />
<br />
I think that you are working backwards. You need to plan what you want to do then get advice on the best way to do it. Over the years I have seen so many people ask questions and so many &quot;experts&quot; answer that question instead of suggesting a better way.<br />
<br />
I have built a similar &nbsp;Leave Tracker for work, but I always make a master table that contains all staff by department.
</div>


<div>
In our Office Roy,<br />
Nobody has Access, it is not part of our Microsoft package from corporate.<br />
<br />
I agree that would probably be easier.<br />
<br />
Before I took this over there were 5 workbooks with the data spread all over the place and very difficult to track anything. What I have now is a huge improvement, but has that one draw back.<br />
<br />
Even if I have one workbook that pulls the data into one sheet that is linked, I can use that second sheet to link all the other workbooks.I know it is not perfect, but I do not write code so it is what I have to work with at the moment.
</div>


<div>
Where are the workbooks stored? Are they all on your PC in the same directory?
</div>


<div>
They are on a server in different folders.
</div>


<div>
I'll have a look at some code. I won't know the path to the workbooks though so I can add a browse for file dialog.
</div>


<div>
The main data folder is &nbsp; Z:\Vacation Calendar\Calender 2016.xlsm ... oh and the sheet is ....... &nbsp;Data<br />
<br />
This is where the tables or sheet needs to go: Z:\One Point Lessons\OPL Matrix\OPL Matrix.xlsm .... and this sheet can be whatever..... also say &nbsp; &nbsp; ... &nbsp; Data &nbsp; .... for the sheet name I guess. <br />
<br />
the Z: &nbsp;I will have to change later as it is a server address<br />
Thanks Roy
</div>


<div>
Thanks Roy, I have moved onto another project.
</div>


<div>
<div class="content wysiwyg-content">
Hi Experts, <br />
I have a workbook that has 5 tables, I need to copy these tables to another workbook (remote). I can not use the same workbook.<br />
<br />
When I add a name to a table in the original workbook it should mirror exactly the same way in the remote.<br />
If it is easier to mirror the same sheet that is fine too.<br />
Which ever is easier.<br />
<br />
The original is password protected...... 7989kerry &nbsp; .....<br />
<br />
Thanks. I have attached to sample WBs<br />
Chris<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/09_w40/1119330/original.xlsx" target="_blank" class="file-inline" title="original (27 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>original.xlsx</a><br />
<a href="https://filedb.experts-exchange.com/incoming/2016/09_w40/1119331/remote.xlsx" target="_blank" class="file-inline" title="remote (9 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>remote.xlsx</a>
</div>
</div>


remote.xlsx

Hi Experts, 
I have a workbook that has 5 tables, I need to copy these tables to another workbook (remote). I can not use the same workbook.

When I add a name to a table in the original workbook it should mirror exactly the same way in the remote.
If it is easier to mirror the same sheet that is fine too.
Which ever is easier.

The original is password protected...... 7989kerry   .....

Thanks. I have attached to sample WBs
Chris

Excel - Clone tables or sheet to another work book.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.