+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 201 to 243 of 243

Copying rows to new sheet then emailing findings.

  1. #201
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    nailed it!!1

    found the file path and got it importing like a charm. Brilliant!!!!

    have also figured out how to import the ODR,csv with out the naming convention. so we no longer require the naming convention box. I think that will make it more soldier friendly lol.

    had 1 little problems when importing though. Also a fault I noticed when I made amendments to the category and Level tables. not showing on the table.

  2. #202
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    on this you can see the Compile error.

    you can also see the Amendments I made to the CAT/Level tables that have not transferred to the Printing page.
    Attached Images Attached Images

  3. #203
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    Have also been asked a question!!!

    My Boss loved this spread sheet, he asked if it would be possible to have a master ODR held at RHQ. with the ability to import up to 6 Sqn.

    each Sn would maintain there own ODR/Roster documents.

    so on this Master document I would input the location all this information is held, like on the Folder on the import sheet, but for 6 locations. thenhit import and watch the CSV and roster file get imported to the master spread sheet.

    is this even possible. as I would give a Regt overview.

  4. #204
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    each sqn would have its own personalised pathway.

  5. #205
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    As for downloading with or without the naming convention. You can either take the file "as is" (with the date stamp) or use the override to "lock" the CSV file to ODR.csv and rename the file after downloading.

    As for the SetChartColor issue: there is a hidden sheet, Color Chart, it defines the categories and what color they should be assigned. The purpose of this is to set the data series color consistently for example due within a month is always orange, overdue is always red, etc. to match the same color codes on the other sheets. I'd have to see some real data so I can duplicate the error. One thing you can do is check the Chart Name. It should be "Chart 1" - however since it is the only chart on the sheet I could refer to it by its chart number (1) as opposed to its chart name.

    As for gathering up all the data and rolling it up to regimental level: this can be done, if someone has at least read permission to all the pathways. I imagine someone at regiment does. It would be a matter of opening up a pathway, collect the data into a master sheet, open the next pathway, append the data, and so on until all squadrons are done. This program would be run by a master workbook that has a "map" of what the pathways are.

    The part of the program that does the consolidation would replace the import data function and the rest of the program can be "bolted on" as is.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  6. #206
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    I can supply all the path ways to the master sheet. As I will be putting your program into all the set locations.

    That loved the program. It's knocking their socks off.

  7. #207
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    just re-ran the data from my desk top, on the Roster tab the amendments I have made are all correct, but these are not being passed onto the report tab.

    I have attached pics to show you what I'm talking about.
    Attached Images Attached Images

  8. #208
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    The charts look good but the data grid doesn't seem to be copied over. Let me look into that.

  9. #209
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    yeah, I noticed it today. I checked 3 times to make sure. lol

  10. #210
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    I'm going to let you fix this one yourself. Might as well get some practice getting your hands dirty with the code with something simple.

    Open the file and press ALT-F11. This opens up the VB Editor. Double click on ModMain. This opens up the main module. This module contains the macros that import the data and parcel it out to the various sheets.

    Add the line of code shown in red in the code snippet below and save the file. The code occurs at the end of the Import_Data macro, just before the FindDue macro. Save the file and you can close the VB editor.

    ClearPivot is a "standard" piece of code I picked up that refreshes all the pivot tables in the book. I think that is what was happening to the data grids: they were not updating when the data changed. By putting ClearPivot where I put it, the pivot tables will be refreshed immediately after the data are imported.

    Please Login or Register  to view this content.

  11. #211
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    sorry to say that never worked
    Attached Images Attached Images

  12. #212
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    done another sqn today. when I completed the import, all the information was correct, apart from the tables on the report sheet, the pie charts where good. the % and numbers on the tables above where incorrect. my though is they aren't being pasted across correctly.

  13. #213
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    figured out what is happening, all the data is bang on!! in the METRIC and ROSTER sheets, its not being copied across to the Report sheet. I have looked but cannot see how to clear the data on the report sheet and re paste the new updated tables.

  14. #214
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    Try this version. Hopefully, this will be the last tweak for the squadron level version.
    Attached Files Attached Files

  15. #215
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    yup, nailed it. tested on 2 different docs. working perfectly. I see you now have the unit roster attached

  16. #216
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    well nearly there.... just looked at this, the METRIC is still not importing to the report sheet.
    Attached Images Attached Images

  17. #217
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    Got asked today if there was a possibility of having personnel who are down as R1 on the roster sheet. If they are within 1 month/out of date etc. they get automatically put onto another tab. that even possible?

    For quick reference, as the R1 personnel are the main priority for trg

  18. #218
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    Sorry about this, was suggested today. as a commanders assessment. will give the RHQ the happy feeling that all R1 personnel are in date, ready for 48 Hr world wide deployment.

  19. #219
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    Let me look. I think I have the data organized (or could get it organized) that way so all such queries would be trivial. I can probably come up with tables and charts that show, here's your R1 personnel (table by name) and here's the percentage trained in each area (chart).

  20. #220
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    That would be awesome, really would love to get this nailed in place and set up. Then leave them to use this system all week, whilst I'm away, see how easy they find using it. Some are not very computer literate.

  21. #221
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    In preparation for the next step, rolling up by unit, I did some maintenance “under the hood.”

    As far as what you see, there is a new field on the Instruction Sheet for Unit Name. Fill this out carefully the first time the spreadsheet is used. The reports and charts now have titles that reflect the unit name.

    When we roll up the report, higher headquarters may want to not only how the overall command looks, but they might also want to see if any particular unit is “driving” a metric.

    I can do this two ways, and I probably will do it two ways:

    1. Give them a dashboard where they can select unit by unit, or overall

    2. Produce a PowerPoint presentation with the roll up on the first slide, and followed by the same metrics for each unit. I assume you have PowerPoint. If I do this I would like your help in building a “template,” that is, how would you like it titled, do you have a unit insignia that you’d like to show on the sheets, etc. I’d like to get an outline with “blank” sheets. I can give you more details when we get closer to this functionality.

    I also added a new report, per your request, to show R1 and other category people by their status (Current, Within a Month, etc.) – This report goes on the summary sheet that gets mailed.

    This version of the sheet is “naked,” that is, all the sheets and columns are exposed. I left it this way for purposes of testing. In the production version, I’ll have things hidden again.
    Attached Files Attached Files

  22. #222
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    Regt Emblem
    Attached Files Attached Files

  23. #223
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    Dflak, tried running it, same folder.

    had a run time error
    Attached Images Attached Images

  24. #224
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    Nice looking.

    I am really lousy when it comes to developing PowerPoint presentations. I'm not an artist and although the slide show is informative, it's rather bland. So that's why I asked for help with a layout. Maybe you can find someone with talent to help with the layout. The attached gives and inkling of what a final product could look like. I am a bit concerned that it will take three pages to show data for one unit. That's a bit busy for a boss to read. I don't have roll up data yet, so I just put in a one-page placeholder. Also I'm sure I get the names wrong, but this is a starting point.

    Also attached is the template I use on one of my reports. The layout was mostly the work of my predecessor. It will also give you ideas. I can replace dates and text.

  25. #225
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    yes, I can come up with a PPP that's easily enough done for me.

  26. #226
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    yeah Three Pages I a lot, they try to work off the A3 size piece of paper. but its better than what they have at the moment, which is nothing.

  27. #227
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    This is confusing. I got the exact same error (which doesn't make sense were it occurs) when I ran the program. So I stepped through the code line by line and it worked. I ran it again and it worked. Just to be safe, I put in a couple of DoEvent statements. That basically tells Excel to take a breather and let things catch up. Now I can't reproduce the error. I'll get back with you on this tomorrow.

  28. #228
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    yeah no problem, thanks for your help, are you sending the attachments from your previous message. so I get an idea and can start to come up with a PPP tile?

    not had the Emm moment yet lol

  29. #229
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    Here are the attachments - I don't know why they didn't go. I'll Zip them this time. - They still are not coming through. I keep getting an uploaded file failed error. I have another trick up my sleeve. I'll try it tomorrow.
    Last edited by dflak; 02-15-2017 at 06:27 PM.

  30. #230
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    Here is the prototype for the rollup.
    Attached Files Attached Files

  31. #231
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    looks great, one question though. all my sqn ODR 11.2 will be in different folders. where do I put the file paths for each of them ?

  32. #232
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    What you are telling me is that the squadrons will not be in the subdirectory of the rollup report. This is not a big problem. I can add it to the map on the Instruction sheet of the rollup program. It's a very small code change, but more typing for you guys

  33. #233
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    I told you it would be an easy fix. Put the path in column B.
    Attached Files Attached Files

  34. #234
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    wow super quick

  35. #235
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    That is Amazing, star!!! will get them loaded up tomorrow.

  36. #236
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    hey, love what your program is doing but had a few quirks,

    when the sqn input their name onto there ODR sheet Their name is not being transfered on the Roll up. its staying as Sqn 1.

    i have got 3 sqn of data. but when rolled up I can only see 2 sqn and same as above Sqn 1 and 2.

    when i select a sqn, the tables are not all changing to thats Sqn. they are priducing a mix of information.

    some of the Bar graphes are not showing the info either.

    when i have not selected any Info and look at the Regt as a whole, its saying thats its 2 sqn info.

    just a few tweeks. but they love the process and the idea of it.

    thanks so much, for all your help.

  37. #237
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    I have no idea why the squadron name would not change. That is nowhere in the code at all. It is read from the data entry on the Instruction sheet and is populated only when the data is read.

    Without seeing it firsthand, it's difficult to tell what's going on.

  38. #238
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    well i have done it with only 72 people, between 3 sqn. as you can see the data is incorrect.

    there is only 2 sqn showing. on the select tab, but definently 3 diffrent locations.

    when i selected the sqn tabs im getting a mix of data from the Sqn.
    Attached Images Attached Images

  39. #239
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    Here is the executable without data. You will have to import data.
    Attached Files Attached Files

  40. #240
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    same thing happening??? very frustrating

  41. #241
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copying rows to new sheet then emailing findings.

    Try a different computer.

  42. #242
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    i only have my laptop, will pop into work and try it when the wife gets back in. Any idea why my roll up is not getting the correct data?

  43. #243
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189

    Re: Copying rows to new sheet then emailing findings.

    This data works brilliantly

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2014, 12:12 PM
  2. MATCH, LOOKUP on other sheet, SUM findings
    By CHRISOK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-28-2013, 10:15 AM
  3. Preventing duplicate entry of a row in Sheet 2 while copying selected rows from Sheet 1
    By Pavan Renjal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-31-2012, 04:27 PM
  4. Copying All the visible Rows in the current sheet and pasting it to another sheet
    By vickyyar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-18-2012, 08:46 AM
  5. Copying rows in one sheet to another sheet based on defined criteria
    By arvin_tx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2012, 10:49 AM
  6. Replies: 1
    Last Post: 06-17-2011, 04:18 PM
  7. stopping copying and emailing of an Excel file
    By Josh C in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2007, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1