Posts by Kiwifinny

  • Aggregate daily time sheets to weekly reporting

    Hi
    I have a spreadsheet which works but it is way too slow - what I'm trying to do is to aggregate daily time sheets by person and job, determining the unique combinations of people and jobs then adding the total values for those combinations by week number.
    I've used concatenate to determine the unique combinations and then nested if statements in an array sum formula to get the answer but each time it calculates it takes up to 5 minutes.
    I have around 6,000 initial daily records but have reduced that in a workbook which is attached.
    The workbook has 3 sheets, the first with name, job and values, the second with the same amount of rows concatenating the name and the job and providing numbering of unique combinations, and the third worksheet references the numbering and carries out the conditional array sum on the original data.
    I'm wondering if there is a more classical or VBA approach to get the DataTransfer worksheet result?
    Many thanks in advance.
    John

  • VBA Copy & Paste between Workbooks Error 1004

    Jonathan
    Many thanks, will give this a go - I don't quite understand why the run time error occurs and am surmising that the macro is trying to run faster than the computers accessing the files from the server hence I introduced the "wait" line. This was based on the thought that the macro had copied the data and was switching across to the other worksheet and pasting it before the computer had caught up. Any thoughts, any would be welcome and appreciate you're a busy man, I always want to understand the why :)
    John

  • VBA Copy & Paste between Workbooks Error 1004

    Hi everyone.
    I have a macro to copy a range of data from one workbook to another and have developed the following code.
    It works fine on my machine but when I load it on to the server it displays a run time error on the paste line.
    Would appreciate any advice.
    Many thanks
    John


    Option Explicit
    Sub D_TransferToProductionShiftReport()


    ' This macro copies confirmed schedule from S&Op planner to Production Shift Report Workbook.


    Dim SCHED As Workbook
    Dim PROD As Workbook
    Dim LastRow As Long

    Set SCHED = ThisWorkbook
    Application.ScreenUpdating = False
    Application.CutCopyMode = False

    If MsgBox("This will open the Production Shift Report and copy the current schedule, do you wish to continue? You will not lose current data.", vbYesNo, "Copy") = vbNo Then Exit Sub

    'Change the address of the Production Shift Report on the following line
    'Set PROD = Workbooks.Open("C:\Data\Intent\Hubbards\1807 Provided to Hubbards\180818\180808 Production Shift Report.xlsm")
    Set PROD = Workbooks.Open("W:\PRODUCTION\Planning\Intent Planning\180808 Production Shift Report.xlsm")
    If PROD.ReadOnly Then
    MsgBox "The Packaging Shift Report is open, please organise to have closed and try again."
    Exit Sub
    End If

    PROD.Sheets("Schedules").Activate
    PROD.Sheets("Schedules").Range("SchedRecords").Select
    Selection.ClearContents
    SCHED.Sheets("4-Prod S&Op").Activate
    Range("D_ProdRecords").Copy
    PROD.Sheets("Schedules").Activate
    PROD.Sheets("Schedules").Range("A5").Select
    Application.Wait (Now + TimeValue("00:00:03")) 'wait 3 seconds
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A2").Select

    PROD.Save
    PROD.Close

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Range("A2").Select
    MsgBox "You have successfully copied the scheduling data into the Production Shift Report Workbook"

    End Sub

  • Reducing Time sheets to weekly summary

    People
    I have a time sheet within a Workbook that has thousands of rows of data and in order to simplify I wish to run a macro across it to reduce it from daily records to weekly.
    I was thinking of a macro to loop through the daily records but ame unstuck conceving the formula to sum daily records for either the same person or the same job.
    Each record has a name of person, a job name and number, a time in hours, a date and a value charged with potentially multiple occurrences of the name and job occurring within the proposed summary week.
    For example on a daily basis I may have
    John - Tree-hugging - 1256 - 2.5 - 1/0818 - $125
    Baza - Tree hugging - 1256 - 1.5 - 1/08/18 - $60
    John - Perenials - 1244 - 8.0 - 26/07/18 - $85
    John - Tree-hugging - 1256 - 4.0 - 3/08/18 - $165


    Which I want to change by way of macro everytime we load new time sheet data to a weekly basis:
    John - Tree-hugging - 1256 - 6.5 - week 31 - $290
    John - Perennials - 1244 - 8.0 - week 30 - $85
    Baza - Tree-hugging - 1256 - 1.5 - week 31 - 60


    I would appreciate any help and direction form the forum - many thanks in advance.

  • Conditional Formatting; Tricky

    People
    I would appreciate some assistance even if it's just a different perspective.
    I have a range of cells across the page, with each cell representing a time across the duration of a shift - say from 6.00am to 2.00pm.
    I wish to populate the cells with a letter "B", to begin and "E" to end which is all ok if there is only one occasion or period of this occurring, where it gets interesting is where say at 6.30am a task began and it was completed by 9.00am. Time passes and the task is again re-visited, this time say from 11.45am to 11.55am. In which case we have two sets of "B" and "E".
    I'm thinking using match function to see which is closer to the cell requiring formatting (they all do).
    Many thanks for any assistance.
    Regards
    Kiwifinny

  • Determine last occurrence of item in a table

    Hi
    I have a customer transaction table with >700,000 records over a 10 week period by day, each day is identified and within each day there are multiple customers with some having a single line, others multiple lines, I wish to sum the volume based on the customer and the day without including the full 700,000 rows of data.
    I think i first need to determine where the day's transactions end (Ordered by date), then within that set of data determine the number of rows of transactions for each customer. My brain just won't work and/or I just don't know how to accomplish this and would be grateful for any help.
    Many thanks
    Kiwifinny

  • Macro select based on Drop Down Selection

    I am trying to have three actions (separate macros) to run based on a control button so that firstly select from the named drop down list, then push the control button linked to a macro which will look at the named selection and then based upon that selection run the associated macro.
    My drop down list is in a cell named "Choice", the data validation options are "First", "Second" and "Third" and the associated actions/macros are simply colour cell background, insert some simple text and put a border around cell.
    The names of the macros are Background, Text and Border again respectively.
    I have tried the following code with no success and would warmly welcome benefiting from the knowledge and experience of this forum
    Many thanks


    Code
    Sub CaseSelect()
Select Case Range("Choice")
Case ("Choice") = "First"
Call Background
Case ("Choice") = "Second"
Call Text
Case ("Choice") = "Third"
Call Outline
End Select
End Sub

  • VBA Color Cells For Gannt Chart

    Re: VBA Color Cells For Gannt Chart


    Many thanks again guys, your help is terrific, I've learnt a lot both with the many ways you can approach a problem and I'll develop from the code supplied. Again huge thanks.

  • VBA Color Cells For Gannt Chart

    I have been struggling for some time to find a simple code to color index cells that have two conditions, one being the task owner and the second being the time requirement.
    I have a drop down list for populating each row of a gannt chart with the owner, this owner also has an associated number referencing the allocated colour index number.
    To the right of the owner is a basic time line with each column represneting days, weeks or whatever measurement.
    These columns are populated simply with an "x".
    What I want to do is to choose the owner, go across to the time, type in an "x" in the time slots and the cells with an "x" are coloured with the relevant owners colr index.
    I have researched the threads, read my text books, looked up the help and found a lot of references which are around the solution but none which nail it.
    Looking forward to your assistance.
    Many thanks in advance,

  • Gantt Chart: Color Cells Dependent On Criteria

    Re: Gantt Chart: Color Cells Dependent On Criteria


    Many thanks, I've taken the code and tried to develop it a little further, although there are a number of useful elements in Andy's work and Dave's links, they don't quite do what I'm after which is to change the owner of a task and thereby all the associated cells, in that row and identified with an "X", also change to the background colour nominated.
    I've attached a wee zip file with more detail.
    Many thanks in advance!