Solved

Excel - copy content from one sheet to another

Posted on 2011-09-30
23
264 Views
Last Modified: 2012-06-22
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
Comment
Question by:oo7ml
23 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36892259
Can you post a small sample workbook?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 36892286
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36892354
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
 

Author Comment

by:oo7ml
ID: 36892381
@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
 

Author Comment

by:oo7ml
ID: 36892408
@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
 

Author Comment

by:oo7ml
ID: 36892426
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36892548
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
 

Author Comment

by:oo7ml
ID: 36892635
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36892873
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
 

Author Comment

by:oo7ml
ID: 36892970
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
 

Author Comment

by:oo7ml
ID: 36892988
Have the Ascending working... :)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:oo7ml
ID: 36893169
Have it all sorted, thanks
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36893227
You're welcome.  Let me know if there is anything else.

Kyle
0
 

Author Comment

by:oo7ml
ID: 36896001
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36903152
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
 

Author Comment

by:oo7ml
ID: 36903331
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36903504
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
 

Author Comment

by:oo7ml
ID: 36903652
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
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 36903756
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
 

Author Comment

by:oo7ml
ID: 36903852
Cool, thanks, that is perfect, thanks for all your help
0
 

Author Closing Comment

by:oo7ml
ID: 36903865
Excellent help, many thanks...
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36903914
You're very welcome.  Glad I could help.

Kyle
0
 

Author Comment

by:oo7ml
ID: 37004962
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
My experience with Windows 10 over a one year period and suggestions for smooth operation
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.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

16 Experts available now in Live!

Get 1:1 Help Now