Solved

Excel - Clone tables or sheet to another work book.

Posted on 2016-09-28
19
138 Views
1 Endorsement
Last Modified: 2016-10-13
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
1
Comment
Question by:chris pike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 4
19 Comments
 
LVL 7

Expert Comment

by:D Patel
ID: 41819819
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
 
LVL 7

Expert Comment

by:D Patel
ID: 41819830
Perform same operations for other tables also.

Let me know if any help needed on to this...
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41819849
Ctrl+T will only work when you are in editable mode (means excel workbook should not be in read only status)
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:chris pike
ID: 41819858
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
 
LVL 7

Expert Comment

by:D Patel
ID: 41819888
Sorry, It was typing mistake.

Consider the extension as .xls

It's the standard solution.
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41820217
What do you mean by remote?
0
 

Author Comment

by:chris pike
ID: 41820331
Roy its just a second workbook.
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41821133
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
 

Author Comment

by:chris pike
ID: 41821702
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
 
LVL 19

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41822913
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
 

Author Comment

by:chris pike
ID: 41823438
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
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41823779
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
 

Author Comment

by:chris pike
ID: 41823833
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
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41824511
Where are the workbooks stored? Are they all on your PC in the same directory?
0
 

Author Comment

by:chris pike
ID: 41825850
They are on a server in different folders.
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41825880
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
 

Author Comment

by:chris pike
ID: 41826288
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
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41826672
I'll see what I can do
0
 

Author Closing Comment

by:chris pike
ID: 41842159
Thanks Roy, I have moved onto another project.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question