Solved

Provide a Copy Paste of One Worksheet into another with criteria

Posted on 2014-01-27
24
255 Views
Last Modified: 2014-02-04
Need to find a solution that will aggregate the bolded data rows, after row 6, minus the Headers, and move them to a Report Template(.xlsm).

1. The Sheet Names of one, should  be copied to the the sheet name of the report template.
2. The Sheet Name "Error Items," of the actual report, do not have to be copied.
3. The column widths, within the Daily Report Template, need to stay as they are set.
4. The starting location can be either row 4 or 5, as the Headers will be copied to several locations within a Word.doc.
5. The data within the Template can  be deleted or replaced each time the macro is run, and the new data can start at either row 4 or 5.
6. Each Sheet, within the template, should be sorted (A-Z), with the main criteria, being the column with the yellow text.
7. "Total items:" does not need to be included.
8.The formatting should be White Background and Black (Automatic) Text, for uniformity.
9. The last tab,  Posted Late [Date] PM will be placed in the Posted Late worksheet.

Attached is an example of a report, and the Daily Report Template.

Thanks...
Daily-Report-Template-v1.xlsm
Error-Log-Report-Test.xlsx
0
Comment
Question by:Cook09
  • 11
  • 10
  • 3
24 Comments
 
LVL 18

Expert Comment

by:Rartemass
Comment Utility
Is there a reason for the multiple tables on one sheet?
It looks as if each sheet is divided into different destinations or origins. It adds complexity to the macro to keep the data split as is. If all the data on a sheet (Late & Missing for example) is in the one table in consecutive rows it makes it simple to copy and paste a large range. IF you then wish to sort it you can use an autofilter to only see the CIC destination for example.
If they are split then we need to add in checks for every row to ensure they contain a tracking number instead of a heading etc. This will slow the macro down.

My recommendation is to have all items in the one table to copy. Apply an autofilter to view specific groups of data as you see fit. I also recommend the total items to be a formula to count non empty cells. Basically something like this for every sheet:
example layout
From there the macro is quite simple. The macro code for a single sheet would be similar to this. You would then need to duplicate this for each worksheet to copy. The code copies the data and pastes only the formulas and number formats. This will keep the destination formats for font. I made it autofit the width of the data. It then sorts on column D (Destination in this case). The macro then selects the first cell in the data range so nothing is selected, and cancels the copy mode in the origin file.
The cell ranges are large to cater for an unknown number of rows. If you need more than 250 you can change it easily.

Sub Macro()

    Windows("Error-Log-Report-Test.xlsx").Activate
    Sheets("Late & Missing").Select
    Range("A10:I250").Select
    Selection.Copy
    Windows("Daily-Report-Template-v1.xlsm").Activate
    Sheets("Late & Missing").Select
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("A4").Select
    Columns("A:I").EntireColumn.AutoFit
Range("A4:I250").Select
    ActiveWorkbook.Worksheets("Late & Missing").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Late & Missing").Sort.SortFields.Add Key:=Range( _
        "D4:D250"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Late & Missing").Sort
        .SetRange Range("A4:I250")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A4").Select
    Windows("Error-Log-Report-Test.xlsx").Activate
    Range("A10").Select
    Application.CutCopyMode = False
End Sub

Open in new window

0
 

Author Comment

by:Cook09
Comment Utility
The worksheets are how we get them as an export from a third-party program.  These have to eventually be copy/pasted to a Word.doc.  The main individuals who would use this have very little experience with Excel/VBA.  There are also a number of other reasons for this format. At the moment it's a manual copy/paste for each individual item.  That gets tedious and one can miss items.

 I did receive some assistance from   gowflow   on something similiar...it was to continually add the items for a comprehensive database.  But, this is different in that it's just a one-time shot.

Cook
0
 
LVL 18

Expert Comment

by:Rartemass
Comment Utility
In that case more needs to be added to line 5 of my code.
Basically a loop will need to check each row to ensure it contains data you want and paste it over.
Alternatively if you don't mind a little data destruction we can delete all the heading rows first then copy what's left over.
I'll need to take some time playing with this as its been several years between coding projects so I'm a bit rusty.
0
 

Author Comment

by:Cook09
Comment Utility
Rartemass,

I don't really want the destruction, and don't have time as I need something by tomorrow.  Can you take a look at gowflow has done and make some changes to it?

Cook
0
 
LVL 18

Expert Comment

by:Rartemass
Comment Utility
Sorry, I've had a really busy day at work today and haven't had a chance to look into this. I won't be able to get this done in your time frame.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Will attack this soon, sorry for delay (that is if no one beats me to it !!! :))
gowflow
0
 

Author Comment

by:Cook09
Comment Utility
gowflow -
  Prior to starting, let's discuss this for a moment...I tried something that was close to what I think I wanted, but it may actually cause more work.  The problem is with the Headers, when we do a copy-paste to Word, the Header is also copied and pasted into the respective City.   However, having them sorted by the City under the Yellow Text is also very much needed.

One tool that may actually be more beneficial at this point, and/or needed in the future, is to compare the data rows that are bolded in Excel with the bolded data rows in the Word.doc. The criteria being the Tracking #.

Or, instead of a Manual Copy/Paste, the code could automatically do the copy/paste. (The Ultimate Wish).  Unless, we move the entire process into SharePoint.

The tool would just examine the data among Excel and Word.  If there are 30 bolded rows amoung the four sheets, there should be 30 pieces of bolded data rows within the Word.doc. The second criteria is that it also match the Tracking number.  The reason for this, is that its easy to spot the City,  however, with one of those long Tracking numbers, one could believe they are the same, but in reality, the last two or three numbers could be different.  Again, this is another question, at some point...but there is quite a few items that I need to accomplish.

When you have the time, I have, what should be an easy question for you.  It deals with taking a macro that I'm using in my Personal.xlsb sheet, and moving the macro to other people's computers, so they can use it.  I know there are several options, but would like your input.  Let me know when you may be able to answer the question, then I'll post.

This project, in its bits and pieces, is beginning to evolve, and others are taking note.  It has to be done, somewhat piecemeal, as we don't know the responses that the users may provide, or what may be the best approach.

Cook
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
@Cook09

In your last post you refer all along to comparing Excel and Word and you refer to a file 'Word.doc'

What is this ?? I don't see this file in the post of this question neither any reference to Word altogether. Did you quote in here by mistake and wanting to quote in an other question ???

I am sorry to advise you that if you need code with Word I am not a good candidate !!
Let me know
gowflow
0
 

Author Comment

by:Cook09
Comment Utility
The comment about the Word.doc was just an example of items that I need to accomplish.  The most immediate need is to take what I have now and move it to someone else's PC (by tomorrow).  There are some code changes that have been requested, but they should be minor.

One issue is the path to the application subdirectory.  When I map it, via Windows, I get the network path, but when I try to use that for a:

vPath = "\\pathname"
Chdir vPath  ' it errors out

If I use the drive Letter (R:) for the main path, then:

Chdir "R:\folderneeded\temp"  it works.  That's puzzling.  But, I can map their drive to R: and get it to work, it should.

That's odd.

Right now, I'm just trying to figure out the best way to take the macro and make it work on someone's PC.  Then there will  be a couple of other questions that I can post.  Part of it will be enhancements to the move all bolded rows to a single database.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry but seems you are stressed out and do not provide any code to fix !!!!
the files you posted have no code What do you want us to work on ??

TAKE TIME AND CONCENTRATE ON THE QUESTION it will save you and save us time !!!
You seems to be time pressed each and every time you post a question and when we post replies it take you ages to reply !!

Don't forget people follow your thread, and when they see this inconsistency no one will consider the post serious which will result in no attendance for your questions.

So presume you need to get organized and all will get in order like no sweat !!!
Regards
gowflow
0
 

Author Comment

by:Cook09
Comment Utility
gowflow--
Okay..I need to do some fixes to the code and will come up with something more concise.  Yes, it is stressful, at the moment. This can have some serious potential, considering we do have 275,000 employees, and I don't want to mess it up.

Some of the other comments were areas that may have to be explored at some future date.  I will try and finish the code that I need to, and read up on whatever method is best for putting it on someone's computer that will allow for easy updates.

Cook
0
 

Author Comment

by:Cook09
Comment Utility
gowflow-

After discussing this with the end users, and verifing with Audit, the next step, which would aid in reducing the "human factor errors" would be to automate or reformat one page of the report as follows:

There is a worksheet called, Late & Missing, the items are grouped by "Origin."   However, as you can see from what I uploaded above, it actually goes on the report by "Destination." I would like to have the report reformatted, so that under each of the Headings, there is a grouping by Destination and not Origin, as not only that worksheet is, but the others as well.

There may be more destinations than Headings, and in those cases, the heading is duplicated and the destination cities (or city.)

While I'm sure you may have a cleaner concept, it would seem that the destination cities are grouped together, with three or four rows in between each, and then a Heading is copied and pasted in between each group.

When I make the couple of changes to the reformatting macro, I'll upload it and you can see where this may be placed within the code, or as a function, to be called after everything else has been completed.  An example of the report is included with the original question.  The orange text would not be there as that was only used to verify that a manual copy/paste was complete.  With this scenario, there would be no worry as each group could be all done together.

Cook
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

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I presume for both your comments that it is only explanations and the real core of what you need to be done is not posted yet. Pls correct me if I am wrong as in EE it is a short and sweet question that users ask and we answer as you specifically asked in the bolded rows previously as here I feel you need a project altogether.

Anyway will wait to see a clearer picture from you.

In any case, just a note, when you say over 275000 employee company, then may I ask you the simple question why don't you get dedicated hired work ? Are you aware that experts do offer paid services and you can contact them on their profile ?

Anyway will wait to hear from you.
Regards
gowflow
0
 

Author Comment

by:Cook09
Comment Utility
gowflow,

I'll address the corporate issue in a bit, but let me provide a concise problem that needs to have a solution.

There is a worksheet called, Late & Missing, the items are grouped by "Origin."   However, it actually goes on the report by "Destination." This one worksheet within the report needs to be reformatted by Destination Groups. Therefore, under each of the Headings, there should be a grouping of cities by Destination and not Origin.

There may be more destinations than Headings, and in those cases, the Heading is duplicated and the destination cities (or city) is placed underneath the Heading.

Attached are two files: One is an "As Is," the other is a "To Be."  They should demonstrate and/or model exactly what is needed.  The only worksheet that needs this reformat is the "Late & Missing," it's tab is in purple.

Cook
Error-Log-Report-As-Is.xlsx
Error-Log-Report-ToBe.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok noted. Is this what your looking for ?
Position yourself on the sheet Late & Missing and activate the macro GroupByDestination and see the results.

gowflow
Error-Log-Report-V01.xlsm
0
 

Author Comment

by:Cook09
Comment Utility
It looks like you've nailed it!  I'll need to reformat the column widths, but I already have the code for that little process.

Is it possible to add the words "Late & Missing"  to the Headings?

I manually inserted it in row 7, prior to running the code, and the macro picked it up.  But how would the code add it to the heading prior to copying it?

But the overall process is exactly what will save us time and increase efficiency....Excellent!

Cook
Error-Log-Report-V01a.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You mean in row 7 and every row afterward that look the same and in row 6 ???
What is the standard the one you posted before that will need to modify to the actual you posted ?
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Is this what you want. Pls try it on sheet Late & Missing (original) I got the copy from the other workbook as you only posted a modified one it is there as new so you can compare.

Let me know
Regards
gowflow
Error-Log-Report-V02.xlsm
0
 

Author Comment

by:Cook09
Comment Utility
gowflow

It works just as imagined and expected.  I could close this out with accepting the above, but given the issues last time, is that what is needed, as you have more than fullfilled expectations?  At some point I can fill you in on possible needs where I work, but given its complexity, I'll save that for another time.

 I now need to look at what should be the next question, already have an idea, but, the problem is probably someone else getting the question, not knowing what has already been done, and what we are trying to accomplish.

So far this has become  mandatory (as of last Thursday) to use within our department (I have to train those on its use), and who knows where it could go from here.

Cook
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Thank you for your comments.

You may close this question if you feel that you got the solution. I from my side will keep this question monitored (ie in my immediate list view) so that when you need something you can post a link to a new question in this question and I will be notified immediately and could attend if you need my help.

Questions are independent and should be finalized each and every time you get your solution. This way you reward the Expert who has put work for you. I am talking in general and not specifically to me !!

Godd Day
gowflow
0
 

Author Closing Comment

by:Cook09
Comment Utility
Exactly what I needed.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Thank you and pls feel free to post a new question link in here if you need assistance.
When you post the new question simply copy paste the address of the new question in here.

Btw you did not advise whereabouts the possibility of having dedicated services which I frankly believe you would have much more to gain than to loose.

It is not always what appear the cheapest that is the cheapest !! and also likewise not always what appears expensive to be expensive.  One has to look  at time and end result and most of time what seem to appear cheap end up expensive and VICE VERSA !!

Keep it in mind.
gowflow
0
 

Author Comment

by:Cook09
Comment Utility
gowflow,

With the exception of a couple of questions, this project is close to becoming, well as finished as it needs to be for the moment.   However, there will be projects that include Excel and SharePoint.  I know they don't always play well together,  but I've read that PowerPivot may help to mitigate that aspect.

How well are your skills with PowerPivot, or Pivots? I guess it depends upon the size, but also Dashboards, in combination with SharePoint?  There may be (will be) some that we have, but I know other departments are going through similar growing pains.  And what would be the cost, for a project, or project support?  I know the scope and size has a lot to do with it, but in certain departments, they use Contract personnel, and maybe they wouldn't have to for specific projects, or project support.  

I don't know that many of them are that difficult, with the exception of moving data in and out of SharePoint, which can be done through an external workbook, if there are macros involved.  Some of what I have needed assistance with, partially to save time, is about as complicated as it might get, with the exception taking data and turning it into a Pivot Table, and then into a dashboard.

Would the contract be with EE or the individual Expert?  I know my renewal comes up in April. My project is a little different in that is was done with a "wink-wink," and it didn't subtract from my other duties, so some of my hours were long, and the code was not that efficient (the one's that I did).
   However, after seeing what can be done, I can see more of this being needed, especially taking the data to an Excel database and then creating a Pivot Table which gets exported to SharePoint, or the database gets imported to SharePoint and it's manipulated there.  But, my boss is at the company's headquarters this week, and I'm sure it will come up.

Normally, there is a functional specification that is approved, so everything is spelled out in advance.

For my next question, I may reference this one, but I'm about to "Ask It."  It is in line with what you have done.  Only, at the end of the Late and Missing macro, the two items needed are to have the non-bolded rows removed from each (question 1), and then a document compare, with the previous days, to see if there are any carryovers (question 2). These are the "nice to haves," to help with efficiency.

New Question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28356072.html


Cook
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Tks your long and detailed comments. I prefer we keep questions separate from what you just explained.

As far as paid services let me clarify
We as Experts are authorized to perform paid services and this is outside EE you can consult any Expert by clicking on their name will come up their profile and some may offer paid services and will indicate their conditions and some will not offer paid services.

For my part I can offer paid services just go to my profile and you will see my credentials and then contact me and we can carry conv outside of EE.

You need to be aware that you cannot mix conversation outside EE with questions being asked in here, in other works Experts cannot take advantage of outside information to leverage it out on questions asked in here. As when questions are asked here they are all on the same ground for all Experts and should be available for all Experts.

For sure when one Expert has worked on solving many issues for you automatically he has an advantage as he knows what the code and the issue is all about and that is normal and no problem.

To go back to specific:
SharePoint not familiar with (but anything can be learned and seen if need be)
Power pivots presume like pivot but this is in newer version of Excel I am at Excel 2007 and can be upgraded case needed
This in a nutshell.

gowflow
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 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

7 Experts available now in Live!

Get 1:1 Help Now