• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Excel - copy content from one sheet to another

Hi, i have an excel document that contains 5 worksheets as follows:

1 - ALL TASKS
2 - HR
3 - IT
4 - MARKETING
5 - COMMERCIAL

As you can see above, i have a different worksheet for each division within our company and an ALL TASKS sheet.

Each division worksheet contains a simple list of tasks for that division. I now want to be able to pull and display all of the tasks from each division worksheet into the ALL TASKS sheet as an overall view of all tasks... can anyone help me with this, thanks in advance
0
oo7ml
Asked:
oo7ml
1 Solution
 
StephenJRCommented:
Can you post a small sample workbook?
0
 
Rob HensonFinance AnalystCommented:
Other than copy and pasting the data from the four sheets being the simplest way, I am thinking you might be able to set up effectively a reverse pivot table.

Select the Pivot Table wizard and in step 1 select Multiple consolidation sources. As you run through the steps you can add each sheet as a source.

When you finish you will have a table with all data in one sheet. If you copy and paste values on this sheet it will break the link to the source data.

Thanks
Rob H
0
 
kgerbChief EngineerCommented:
Take a look at this sample and see if you like it.  If so we can tailor it to your specific needs.  Just click the button on the ALL TASKS sheet.

Kyle
Q-27374010-RevA.xlsm
0
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.

 
oo7mlAuthor Commented:
@robhenson: i have tried to add a pivot table however i am unsure what i need to put in each field... i have attached a screenshot

 screenshot
0
 
oo7mlAuthor Commented:
@kgerb: thanks for that but i cannot run macros on my system... IT will not allow them to be run (and i cannot find a way around it)
0
 
oo7mlAuthor Commented:
Cool, thanks i now have macros enabled, however one thing... there are several columns in each field and i was hoping to pull them as a continued list, are you able to ammend it so i can copy that, thanks
0
 
kgerbChief EngineerCommented:
Try this one.  If this is what you're looking for we can modify it so it pulls data from the correct cells.

Kyle
Q-27374010-RevB.xlsm
0
 
oo7mlAuthor Commented:
Absolutely perfect... the first column is a date column in all of them... so is it possible to add a filter by date to the macro so that it will filter the list by date... thanks again...
0
 
kgerbChief EngineerCommented:
Try this.  I made it apply an autofilter.  If you just want to start the autofilter but not actually apply a filter that can be done too.

Kyle
Q-27374010-RevC.xlsm
0
 
oo7mlAuthor Commented:
Cool, that is brilliant… last request (if possible)… can you change the order so that the oldest date is displayed first and also is it possible to add the source worksheet name in another column at the end so you will know where
0
 
oo7mlAuthor Commented:
Have the Ascending working... :)
0
 
oo7mlAuthor Commented:
Have it all sorted, thanks
0
 
kgerbChief EngineerCommented:
You're welcome.  Let me know if there is anything else.

Kyle
0
 
oo7mlAuthor Commented:
Hi, sorry, I actually can't get the worksheet name  at the end of each task in the ALL TASKS - are you able to complete this, thanks in hadvanve
0
 
kgerbChief EngineerCommented:
Hello oo7ml,
Sorry for the delay.  I didn't have time to work on this over the weekend.  See if this is what you're looking for.

Kyle
Q-27374010-RevD.xlsm
0
 
oo7mlAuthor Commented:
Thanks, that is exactly what i needed, however i did notice that the first row always seems to be HR task one after the macro has been run... can you see that
0
 
kgerbChief EngineerCommented:
Sorry, it was messing up b/c of a autofilter header issue.  I added headers to the ALL TASKS sheet and it appears to working normally.  Take a look and see if it's ok.

Kyle
Q-27374010-RevE.xlsm
0
 
oo7mlAuthor Commented:
I have tried to copy that to my file as i have more columns but now i am getting an error... can you see why... i promise this is the last request :) and thanks for all your help...
copy-sheets.xlsm
0
 
kgerbChief EngineerCommented:
I'm not sure why you were getting the error.  I cleaned things up a little bit, added a header row to the ALL TASKS sheet, and deleted the line of code specifying whether the Autofilter had a header since it is unnecessary now.  It seems to be working, at least on my machine.  

One note:
Now that the ALL TASKS sheet has a header row there is no reason to delete it and re-add every time data is pulled.  So, now, the Autofilter is not deleted and added with the code.  The existing Autofilter is just modified.  So, if you deleted the Autofilter manually and then run the code you will receive an error since it will try to modify an Autofilter that doesn't exist.  Maybe that's what was happening.  If you do manually delete the autofilter, just select the Data tab and click the Filter button in the Sort and Filter group.  That will re-instantiate the Autofilter so the code has something to modify.

Take a look and let me know if it's working.

Kyle
Q-27374010-RevF.xlsm
0
 
oo7mlAuthor Commented:
Cool, thanks, that is perfect, thanks for all your help
0
 
oo7mlAuthor Commented:
Excellent help, many thanks...
0
 
kgerbChief EngineerCommented:
You're very welcome.  Glad I could help.

Kyle
0
 
oo7mlAuthor Commented:
Hi, i have a quick query based on the file you provided the last time, which you might be able to help me with.

Column A pulls and sorts the date
Column C is the status of a task

If the date in column A is in the past AND the status is anything but COMPLETE, set the date in column A to todays date, for example:

=IF(C7<>"Complete",IF(A7<$F$1,$F$1),A7)

It would be ideal if we could insert this into your macro so that unfinished activity will be moved to today by sorting, a big thanks if you can help with this, thanks in advance
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

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now