Solved

Excel - Clone tables or sheet to another work book.

Posted on 2016-09-28
19
72 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
  • 8
  • 7
  • 4
19 Comments
 
LVL 5

Expert Comment

by:D Patel
Comment Utility
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 5

Expert Comment

by:D Patel
Comment Utility
Perform same operations for other tables also.

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

Expert Comment

by:D Patel
Comment Utility
Ctrl+T will only work when you are in editable mode (means excel workbook should not be in read only status)
0
 

Author Comment

by:chris pike
Comment Utility
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 5

Expert Comment

by:D Patel
Comment Utility
Sorry, It was typing mistake.

Consider the extension as .xls

It's the standard solution.
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
What do you mean by remote?
0
 

Author Comment

by:chris pike
Comment Utility
Roy its just a second workbook.
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Roy_Cox
Comment Utility
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
Comment Utility
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 17

Expert Comment

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

Author Comment

by:chris pike
Comment Utility
They are on a server in different folders.
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Roy_Cox
Comment Utility
I'll see what I can do
0
 

Author Closing Comment

by:chris pike
Comment Utility
Thanks Roy, I have moved onto another project.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now