Copy & Paste Data Based On Current Month

  • I have created an action plan in Excel, with a column containing the values Yes, No and Partial to show prgress against the actions.

    I have used CountIF (in cells D72 to D74) to count the number of each Yes, No and Partial. I want to be able to put these figures in a chart each month. I am using columns F to Q for the chart data. the headings of the chart columns are month values, e.g, F1 is "6" (for June).

    I have used Month(Now()) to generate the current month value in cell B1.

    I now need a macro that will check the value in B1 against the chart table headings in F1 to Q1 and if it finds a match, will copy the values D72 to D74 into the chart table below the relevant heading.

    Please help!

  • Re: Use Macro To Copy And Paste Data Based On Current Month

    please could you post some sample data... you might not need a macro to do this...


  • Re: Use Macro To Copy And Paste Data Based On Current Month

    See attached. The table I need to populate with data is shaded in blue. The data topopulate a given month is shaded in yellow. The way it works is that a person will update the 'Compliance' column as they make progress, possibly once a month. I then need hat data to be transfered into the relevant month of the blue table, to make a chart with it.

  • Re: Use Macro To Copy And Paste Data Based On Current Month

    Ok i'm a little confused with your table...

    How would i populate the data for Jan, Feb, Mar, Apr, etc...?

    with the data you've provided you could create a chart for Yes, No, Partial.

    would you be able to add a date column in and then you 'could' create a pivot table to collect the data you want?


  • Re: Use Macro To Copy And Paste Data Based On Current Month

    Thanks for trying, I see what you are saying but unfortunately I have to have a single 'compliance' column rather than one for every month, so can't use a pivot table.

    I've thought of a better explanation of what I need... I need a macro that does the following when I click a button:
    1. Find the column heading from e.g. H1 to H12 that has the same value as the value in D75.
    2. Copy the values from D72-D74 and paste them underneath that heading value.

    In this example,
    H1 to H12 are the months of the year.
    D75 is a user defined month.
    D72 is the number of Yes values
    D73 is the number of No values
    D74 is the number of partial values.

    If anyone could just write me the above macro that would be brilliant. The result will be that a person will update the action plan each month, then click on a button to add the latest month to the chart.

  • Re: Use Macro To Copy And Paste Data Based On Current Month

    Try the following (it is not the most elegant but does what I think you want)

    I believe the code can be combined but can not remember how to get the pastespecial to work.

    Range("D70").Copy Destination:=Cells(2, tmp2)

    not sure how to get the pastespecial into the line above to paste just the value

  • Re: Copy & Paste Data Based On Current Month

    It pasted this month's data under July, but I got it to work by changing "tmp + 5" to "tmp + 4". How does that work? (I don't know vba). Could you explain it to me please. How does it work out where to paste the data?

  • Re: Copy & Paste Data Based On Current Month

    The code can be shortend as follows:

    if you look at the code, the line


    calculates the numeric value of the current month (for Jun it results in 6). To check that it is working correctly, unremark the line


    by removing the ' then when you run the macro, it will pop up a box showing the number. line


    added 5 to the tmp number becasue in the sample you posted, your graph data started in column F which is the 6th column. The first 5 columns were your static data. so we need to add 5 to the number of the month to get the correct column number to post the data into. It worked correctly on mine. It it was posting to the July column, then either you have one less static column or possibly the date on your computer is off. by unremarking the MsgBox lines, it will show where the issue may be.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!