Posts by Kiwifinny

Carim
Yes thanks, unfortunately I have to transfer the data into another Workbook that compares actuals with forecasted weekly data, Pivots are great if this was a stand alone workbook.
Many thanks
John

Carim
Many thanks, yes I just picked that up.
I'm wondering if a macro would be the way to go for speed and efficiency, the data is always in the same format.

Thanks
John

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

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

Thanks Jonathan
The macro opens the workbook, so yes both workbooks are open, no hidden rows or columns and no protection??

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

Many thanks, and yes we can use Pivots but we need the summarised data in a flat table for further reporting and interrogation.
Any code?

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.

I went very simply to count the occurrences of letters and format if the number of letters to the left was an odd number.
Thanks anyway.

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

Re: Determine last occurrence of item in a table

Many thanks, the formula works well.

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

Re: Macro select based on Drop Down Selection

Not sure if Quade qualifies as a Kiwi!

Re: Macro select based on Drop Down Selection

Pike
I'm not sure how to advise "solved", any advice?

Re: Macro select based on Drop Down Selection

Pike you're a legend, both have functionality I was looking for - many thanks, you're a legend.

Re: Macro select based on Drop Down Selection

The macros don't operate, I suspect my coding / naming is incorrect?

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

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

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.

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,

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!