Excel - Clone tables or sheet to another work book.

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
original.xlsx
remote.xlsx
chris pikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

D PatelD Patel, Software EngineerCommented:
Hi,
 
Try this.
 
1. Select A1:K21 (in Original file) and assign it a name, say Table1
3. Select A1:K21 and press Ctrl+T to convert to a Table and save the file
4. Click on any cell in Remote.xlsx and go to Data > From Other Sources > From Microsoft Query > Excel Files
5. Navigate to the folder where the file is saved, select the file and click on Next
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 > Symbol
7. Click on Next twice
8. On the last screen, select "Return Data to MS Excel"
9. In the Import Data box, select Table and click on Finish
 
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.
 
Hope this helps.
0
D PatelD Patel, Software EngineerCommented:
Perform same operations for other tables also.

Let me know if any help needed on to this...
0
D PatelD Patel, Software EngineerCommented:
Ctrl+T will only work when you are in editable mode (means excel workbook should not be in read only status)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chris pikeAuthor Commented:
Sorry the attched work book is not in .xlsm  which is the format my original is in... It has a ton of macros already.
In the attached workbook you can see that they are all are tables already.
This is a copy paste from another solution I have already tried this before I posted the question.

Unexpected file format. Does not except .xlsx or .xlsm format, only .xls

Any ideas?
Thanks
Chris
0
D PatelD Patel, Software EngineerCommented:
Sorry, It was typing mistake.

Consider the extension as .xls

It's the standard solution.
0
Roy CoxGroup Finance ManagerCommented:
What do you mean by remote?
0
chris pikeAuthor Commented:
Roy its just a second workbook.
0
Roy CoxGroup Finance ManagerCommented:
Original workbook is password protected.

Why do you need two workbooks?

You could use VBA to update the Tables when the remote workbook is opened
0
chris pikeAuthor Commented:
Roy,
Thanks for the question.
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, "save as" then "tools" then "general" then password to open.

05.JPG

06.JPG
I want to use just the employee names (just the one sheet) which changes a lot.

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.

Does that kind of make sense?

Thanks so much
0
Roy CoxGroup Finance ManagerCommented:
I would suggest that your data should be in one table. That sheet can then be very hidden.

You could then create a lookup sheet to bring up specific data. I would never have 30 sheets for individual data if that is what you are using.

I have set up a similar system at work and I have created code to allow  updates to be imported.

I'm not sure what you want to create from this data but this is how I would set it out to start from

If you can add a sheet showing the layout of your other sheets I can maybe link them to the master sheet.
original.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chris pikeAuthor Commented:
Hi Roy,
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.

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.

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.

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.

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.

Thoughts??
Thanks Roy
Regards
Chris
0
Roy CoxGroup Finance ManagerCommented:
Thoughts:

Maybe you should be using Access. You can link Access Tables to Excel and they will update in the Access Table

From the Table that I uploaded I could easily write code to move  all a department's employee's to another sheet for further work.

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 "experts" answer that question instead of suggesting a better way.

I have built a similar  Leave Tracker for work, but I always make a master table that contains all staff by department.
0
chris pikeAuthor Commented:
In our Office Roy,
Nobody has Access, it is not part of our Microsoft package from corporate.

I agree that would probably be easier.

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.

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.
0
Roy CoxGroup Finance ManagerCommented:
Where are the workbooks stored? Are they all on your PC in the same directory?
0
chris pikeAuthor Commented:
They are on a server in different folders.
0
Roy CoxGroup Finance ManagerCommented:
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.
0
chris pikeAuthor Commented:
The main data folder is   Z:\Vacation Calendar\Calender 2016.xlsm ... oh and the sheet is .......  Data

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     ...   Data   .... for the sheet name I guess.

the Z:  I will have to change later as it is a server address
Thanks Roy
0
Roy CoxGroup Finance ManagerCommented:
I'll see what I can do
0
chris pikeAuthor Commented:
Thanks Roy, I have moved onto another project.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.