Posts by Smudge.Smith

  • Copy rows from one sheet to another based on match

    So, each time I press the 'Copy Macro' button, the number of columns being copied over from the Employees sheet into my table in the Activities Sheet, are duplicated.


    I'm guessing it's because of the line in the macro:

    sh1Col = wsh1.UsedRange.Columns.Count + 1


    So I understand, on first run, sh1Col will equal column F as the starting reference for the employee details to be copied to as the used range is A to E is occupied by data. (Which is correct)

    But then next time the macro is run, say after more data is added (or not) to the Activities sheet, Columns F, G, H, and I are occupied with the data that was copied to them from the previous run, therefore making the starting point to be copied in the .UsedRange.Columns.Count + 1 as Column J and then subsequently, next time, it's column N,... R and so on.


    How do I prevent this so my table stays within the bounds of Columns A to I ?


    Thanks in advance


    Smudge

  • Dynamic Chart Title Using Pivot Table and Slicer

    Haha that's great - but I just read through the link you kindly supplied and came across the reference to CubeRankedMember and @GetPivotData and the associated VB... which I've implemented and is much more elegant.


    Many thanks for your prompt and valued input. Very much appreciated as always. :thumbup:


    I do have a question regarding a previous VB issue you helped me regarding this very table, but think to keep within the bounds of forum etiquette, will have to raise it there.


    Many thanks once again! :)


    Smudge

  • Dynamic Chart Title Using Pivot Table and Slicer

    Hi


    I've been playing around with a simple dynamic chart title based on selected buttons on a slicer.


    In this example I have a chart that I want to display titles by area.

    The chart title is populated by a simple pivot table $M:$31 and by using a slicer, I text join the selections which the chart reads at $A:$33 into a text box where multiple selections are made.

    As can be seen, selecting buttons on the slicer changes the chart title to the desired effect. However, after three buttons are selected, the title gets messy so what I would like to do is, if ALL buttons are selected, the title reads "ALL AREAS" instead of listing the place names individually. Similarly, if the slicer is cleared and no buttons are selected then the title should be blank.


    Is this even possible and / or is there a more elegant way of achieving this?


    many thanks


    Smudge

  • Copy rows from one sheet to another based on match

    Hi Carim


    Thanks for the rapid response (as usual) ;)

    Having taken some time to watch and understand the video you directed me to and how Power Query can be used to transform data, I thought that this would be the ideal answer as it was inspiring... but having mocked up a recreation of the data to suit my requirements it seems it's not the way forward for me - unless I'm missing something.


    So, to summarise my initial problem (which I probably didn't convey very well). I want to match employees in one table (which is a record of certain actions that the employee performs during the day, over weeks / months etc) against a list of employees and their work details (Area / Team / Unit etc..) provided to me by HR which would allow me to create a report which shows how each Area / Team / Unit are performing based on the data supplied in the initial report of employee activities. (I've changed my mock up data file to reflect this rather than relying on the generic information that I originally supplied). The employee ID is the common identifier in both tables and is the obvious choice to use to get the required data.


    Having recreated the video as a test environment, all I seem to have done is duplicated two tabs of data into four and then added another two to produce a pivot table, which at first sight seems fairly unintelligible as it would appear that the report I want to produce is based on 'blanks'!

    If I filter out the blanks, then my data disappears along with it!


    This is why in my request, I thought the way forward was to match the employee ID with the one supplied by HR and then have some magic formula that copies the relevant HR details onto the end of each row of the employee activity?

    The result being I have all the relevant data on one sheet to pivot and chart to my hearts content instead of six... and no 'blanks' to obfuscate the pivot tables...

    (I think If I gave my boss that to look at, I wouldn't be posting on here any longer... as I'd soon be out of a job LOL).


    Any words of wisdom please?


    Many thanks

  • Copy rows from one sheet to another based on match

    Hi Carim

    Each row is a unique action that the employee undertakes at different times on different dates, hence what appears to be identical rows - but are not. The employee may make several actions at different times during a given day

    The employee data that I want to copy is fake and not the real data - the proper data is more pertinent to the the area, locality and department that the employee works in... which I cannot publish here.

    My overall aim is to report on employee 'performed actions' by area / locality / department etc... and ultimately break it down using slicers to and the only way I can see of doing this is by combining the report of employees actions against the data supplied by employee details, which are provided by two disparate systems in CSV format and so using the employee ID as the identifier, find out where the employee works, I can report how many ' actions' were performed by which area / locality / department etc...

    The table will then be used to populate pivot tables / drive a dashboard etc.


    Hope that makes sense.


    Many thanks

  • Copy rows from one sheet to another based on match

    Hi forum


    I've been away from Excel for a while and need some assistance to clear my brain freeze.


    I have a workbook with a sheet containing employee data and want to copy the Employee details from Column B to F, where the Employee ID matches the Employee Number on Sheet1.


    In my example, Sheet 1 Employee Number matches Employee ID on Employees Sheet and therefore Marga Walkinshaw data is pasted in to the relevant columns.... and so on for the remainder.


    Obviously this is mock data and I have thousands of rows in Sheet1 to fill in so requires a more efficient solution than just copy and pasting...

    Also, FWIW, the original Employee ID / Employee Number comprises of alphanumerical characters and is not a true number.


    Any help greatly appreciated.


    Smudge

  • Optimise Multiple Worksheet_Change(ByVal Target As Range) - worksheet slows down as more rows added

    I have two functions that operate 'live' on a worksheet:


    1, Fill cell ("A",i) with a pre- determined colour based on the number keyed in at that location.


    2. For a date that is keyed in at ("L",x), do one of three things:

    If the date is less than two years from todays date, colour the cell next to it green and insert text "OK"

    If the date is more than two years from todays date, colour the cell next to red and insert text "Overdue"


    The idea is that as dates are added to rows in column L, it is compared to today's date and as the date approaches a two year anniversary, it will flag up as yellow and being due, after that, if the date isn't updated, and goes over a two year point then will flag up as red and being overdue.


    This seems to work fine sometimes but as I insert dates further down the rows, the blue spinning circle starts to appear and takes longer and longer to think about updating.

    Clearing a cell doesn't always seem to clear the cell next to it either.

    Could someone take a look at my code and offer words of wisdom to optimise it?



    Additionally I've been trying to add an extra condition to function 2 that does this:


    2. If the date in ("L",x) is within two months of its two year anniversary and becoming overdue, colour the cell next to it yellow and insert text "Due"

    I can't seem to get to grips with the logic


    Many thanks


    Smudge


  • VBA to select rows that contain a selected month?

    Hi RoyUK

    Thanks very much for your prompt assistance. I hadn't heard of EOMONTH before but looks as though it should work based on what I've read.

    I added three extra rows with December dates, unfortunately the code produce a message that it was unable to find the required rows when 12 (December)

    I think there is something missing from your amended code that doesn't take into account the start date of Dec being in the previous month?


    Code
    lFrom = CLng(DateValue(Format("1/" & lMth & "/" & Year(Now()), "dd/mm/yyyy")))

    Wouldn't this assume that the variable lFrom is in 2022 (as I set the clock forward to test?)based on the Year(Now) function?



    Code
    lFrom = CLng(DateValue(Format("1/" & lMth & "/" & Year(Now()), "dd/mm/yyyy")))
If lMth < 12 Then
lTo = CLng(DateValue(Format("1/" & lMth + 1 & "/" & Year(Now()), "dd/mm/yyyy")))
Else
'lTo = CLng(DateValue(Format("1/01/" & Year(Now()) + 1, "dd/mm/yyyy")))
lTo = CLng(Application.WorksheetFunction.EoMonth(Date, -1))
End If


    Many thanks and best wishes


    Chris

  • VBA to select rows that contain a selected month?

    Apologies for revisiting a thread which I had marked as resolved - but I have found an issue which I think I need some help getting my head around with regards to selecting the month to be exported.

    Currently the VBA appears to rely on the selected month being in the current year, so each of the 12 months in this year are available to choose if the report is run in the current year.

    However, in practice, the selected month should always be a month behind when the report is run.

    Eg the month end report for Jan will be run in Feb, June's report will be run in July etc..

    This process falls down when Decembers data is required, as the report will be run in Jan and therefore previous December will no longer be in the same year as the report is run.

    I've been fiddling around with IF statements trying to make it work but unfortunately I can't seem to find the right terminology, especially as we're not actually at a point to test it (ie it's not January yet and I'm trying to get the previous months data).


    Many thanks


    Smudge

  • VBA to select rows that contain a selected month?

    Hello people


    As a continuation of my previous road to enlightenment project ie learn some useful VBA, I requested help to select rows of data that contained the current month and export those rows and selected columns into a CSV ready to be exported into another program. Thanks to everyone who provided advice or updated my code.


    My issue now is how to select any month within a calendar year rather than just the current month. I was thinking of a drop down menu where you can select a month but unfortunately I have no idea how to implement this into my current code. ( I saw something similar elsewhere but unfortunately I couldn't make it work for me so binned it).


    Anyone out there who can set me in the right direction?


    Many thanks once again.


    Also, on another note (apologies to admin if 2nd question is not allowed under the same heading), I export the headers separately as I believe that they are not copied over due to to not being recognised as a date in the error capture routine.

    When the relevant date rows are then copied to the new sheet, I end up with a blank rows between the headers and the data.

    I know I could just delete the blank rows but was wondering if there was a more elegant way to move the data up so it sits below the the headers?


    TVM


    Smudge


  • Selecting rows that fall within the current month

    Hello again


    Further to my recent post about exporting selected Excel columns to CSV which was very kindly resolved by gijsmo , I decided I wanted to build on that and export only those rows where the date contains the current month.

    The idea is that as a monthly report, I don't have to mess about selecting dates etc... the code just looks for the current month in column C and selects those rows for export.


    So I used lr and r to find how many rows of data there are and then count through them to see if the row contains an actual date and if so, if the date contains the current month. The idea is then to copy these rows and selected columns into a CSV file for importing to another program.

    (original VBA columns selection which worked previously is remmed out).


    Unfortunately, when I get to the save as CSV screen, nothing is copied over and I don't know why. I would expect to see rows 3,4,5,6 and 7 appear in the new CSV as their dates in column C fall in May (which as of today is the current month).


    Anyone tell me where I've gone wrong... and point me in the right direction please?


    Many thanks


    Smudge



  • VBA to export CSV not saving dates in UK format

    Hi


    I've got a relatively simple task of exporting selected columns into a CSV ready to be used in another program.

    The macro seems to work as advertised with the exception that the dates do not keep their UK format but are resolved into USA format.

    The locales are set correctly on both my PC and the PC where the original xls file originates.


    Anyone know what tweak I need to make to the macro to make it export the date in UK format?


    I've attached a simplified version of the excel spreadsheet along with the VBA coding.



    Many thanks


    Smudge