Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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
1
chris pike
Asked:
chris pike
  • 8
  • 7
  • 4
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now