View Poll Results: Macro challenge! how to pull information across with certain limitations

Voters
5. You may not vote on this poll
  • Can be done

    4 80.00%
  • Impossible

    0 0%
  • Possible if given basic templates

    0 0%
  • Difficult even if had templates

    1 20.00%
Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro challenge! how to pull information across with certain limitations

    Hi,

    I am looking to see if it is possible for a macro to be created that has a few function requirements which are listed below.
    1) be able to check both workbooks and the internal worksheets and see if have same number of rows for a given code
    2) if new line required to add this line into one of the worksheets. If unable to continue running but highlight missing information
    3) copy & paste cell format from a column to another set of columns
    4) where cells align from the first point transpose information over from one workbook to another
    5) to be able to do this over various worksheets within a workbook

    I have done some basic formulas to see where I can tell where the information matched and cell reference using the match formula etc.

    Any guidance and help would be great fully appreciated. I am taking over from someone else and there workbooks are layed out poorly but i cannot change this due to their layout being the foundation of a much larger workbook feed from these.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    If I understand the phrasing, it seems to me that they should all be possible.

    If you want help in creating the macro, it would help if you requested more detailed and specific information and perhaps even provided an example workbook which contains a range of differences to work with. Perhaps show us a before and after or walk us through exactly what you want the macro to do...

    Steve

  4. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Thanks or the response and please find attached a cut down version of what i am working with.

    The first Workbook is the titled Master Profile and is sheet i am looking for the information to be transposed onto. This has been cleaned up due to sensitivity of the information contained.Please excuse the format i had to strip it back bare for the file size.

    The second workbook if titled Master Load file and is a copy of the central database which i am looking to extract information from.

    So what i am looking for is that a macro will first check and see if the information is the same to the previous update. I am then needing the macro to then count and see if any new rows have to be entered. If this is required then to insert the new row into each of the required entry boxes ( it is laid out headcount, utilization, man-hours, rates, labour cost and material cost). After it has done those checks and entered any new rows i am looking for the macro to copy the format from Feb14 or whatever the previous load was done and replicate for the next 3-4 months. Once this is done i am looking for it to load in the required information from the Master Load File and everything working ok.

    This is a cycle i will be needing to do every 3months so a macro helping to speed up this process would be great If not it be a be me sitting copying paste this information across.
    Attached Files Attached Files

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    Could you elaborate on what exactly you want the macro to do with the 3 worksheets?

    What would you be copying from and where are you pasting to? Based on what criteria?

    If you were doing this manually, what exactly would you do? Does only one of the 3 worksheets get modified, 2 of them or only 1? Explain what you want to end up with.

    Steve

  6. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Basically if doing it manually i would be going by the code in column B of the Master profile workbook and lining it up with the same information in the Master Load File. If a new line was required i manually insert a new line into first the headcount box (if it was new manhours) then a new row into utlization section, manhour section, rates section and labour cost section. Then i would copy paste in the infromation of the Code etc and the hours and then back fill in the calculations for the boxes. I would then do the same process for Labour costs and material costs. This process takes around a day to do as its over 3,000 lines and into various workbooks.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    You will have to be more detailed about how to do it in the sample. From what I can see in the load file for costs, Cols A-H aren't unique (the same is true for hrs Cols A-D), so am not sure how to compare and match up. Many of the rows seem to be able to match up with multiple rows in the profile sheet.

    So do a detailed walk through, specifics, not genearlities, what sheet do you start in, what cell or cells do you compare and what do you compare it to? If not found what to do, if found what do you do? Do you stop after finding it once, or do you continue to do someting if the match is found again? All of this information is needed to even start thinking about the logic to the coding.

    For this type of searching, using MATCH is often the most efficient, but requires comparison of a unique entry. Even creating that info (with a column of concatenated info) temporarily can help the coding without requiring multiple loops and multiple comparisons.

    If you want us to help, you have to provide us with the necessary details and information to be able to help you.

    Steve

  8. #7
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    If you look at the master load file to the far left of the data you will see i had already started that process by linking in the WBS code with the classification and hours up to end of Feb14. On the Master profile that same information is there as well in column B and column L and the hours total is down below. I Have done this already if want a copy o that showing it lining up?

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    That not only does not answer the questions I pose, it raises other questions since you are indicating that the sample files are intermediate files, not the start and not the end.

    At the very least, please attach a sample of the file(s) you have at the start and also a file of what you want at the end. Maybe from that the logic of what you want will become clear, but I am not optimistic, I still think you may have to walk us through the logic from start to end.

    Steve

  10. #9
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Due to the senstive nature of the task i am unable to fully share the full Workbooks or Load file. Which is why i had to cut it down to a sample of one of the worksheets within a workbook and a sample of the load file.

    The sample Master profile is used by various people to populate there spend profiles going forward on the project and the various workbooks are combined into a master model.

    What i am looking for is a quick way to load these worksheets with the actuals from the Master Load file. It had been done before by someone manually go into each worksheet and populating manually this information but am looking for a quicker way of doing this.

    I had assumed a macro which would count the number of lines already been populated and then compares this to the Master Load file to first check aligns (as said before by the match function) and if required new lines to be instered in. Once that is complete taking format from the Feb14 period and move this right for 3-4months (depneding on the length of information to be copied in from Master Load file.

    I have assumed if the macro works for A.10.01 i would be able to modify it to work upon the ther worksheets and workbooks.
    Attached Files Attached Files

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    Is this file meant to represent a before or after? Where is the other?

    You are not answering the questions being asked of you. You don't need a macro if all you want to do is count, a formula can do that. Match, whether in a formula in a cell or in a macro needs a value, and a lookup range. What value do you want to lookup and what range should it be looked in? What do you want to do with that value once you get it, what do you want if there is no match? You mentioned copying and pasting but you don't indicate what you want to copy and where you want to paste it?

    I need details! If you want a macro to do something to this workbook, please walk me through what you want to accomplish with the macro. What would be done manually with this file to get what you want would be a start to understanding what you may want the macro to do.

    Steve

  12. #11
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Please find attached a before and after copy of what i am looking for. As you will find in the Master Profile attachment it is actuals up to Feb14 and what i am looking for is in Master Profile v2. As you will see the format has been copied forward 3 months, as well as new lines been added in from the missing information for hours and costs.

    The macro i am looking for is to at first count and match that for code A.10.01 (example as other codes are around) and if hours is missing add in new lines for this. ONce this is done to copy and paste in the missing 3 months of information for all the rows.
    Attached Files Attached Files

  13. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    So which is the start, which is the end and what is the purpose of the 3rd file?

    Walk me through the logic of when you have the start file and the "3rd file", how you would get the end file...

    Explain what you are counting, what you are matching, how the code knows that something is "missing" and how the code knows where to add new lines. Also what is copied from and where is it pasted to? I presume that some looping is involved, so walk me through a couple loop iterations where does a loop start, what happens in a loop and where does a loop end and repeat?

    Steve

  14. #13
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The first file Master Profile is start of the file. The code to the far left on that file is looking at the WBS code plus if labour staff etc and the total actual hours. There is a replicant code on the Master Load file to match and check. The 3rd file is the solution I am looking for of new lines being added in if required and actuals loaded in as well.

    So I need the macro to lookup and check that for A.10.01 for hour the total number of lines are same and match or if not to then insert new lines. After this to format lines so like Feb14 and then load in the remaining actuals. This is then repeated for the labour cost and material costs.

    I have done the match and code but when done my own macro i was struggling on how to get it to lookup and check total numbers n

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    Please, I need details. Walk me through EXACTLY what you want the code to do. Refer to workbooks, worksheets and cells. What is being compared, what value(s) need to be looked up, etc? What should be counted, what defines a match, what to do when there is and is not a match.

    Steve

  16. #15
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am looking for the Macro code to be run on the Master Profile workbook. There is already a code written in cell Fp of the Mater Profile and in cell FQ the match reference to Master load file. The Master Load File has simlar code in cell EH and i am looking for the macro to check that first of all these line up and to count and see any other Codes under A.10.01 are missing. This is for both Manhours and Costs. If there is lines missing i am looking for the Code to then insert new lines into the blocks reffered to as Headcount, Utlization, Manhours, Rates, Labour Costs and Material Costs as well as the replciating any calculations within those blocks to the new lines.

    I am then looking for the Macro to copy the format from Feb14 to the right and then copy from the Master Load File the manhours from either September 10 to May14 or from Mar14 to May 14.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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