Copy & Paste Data Based On Current Month

petehenson · Jun 29th 2007

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!

Craig Ottley · Jun 29th 2007

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...

z

petehenson · Jun 29th 2007

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.

petehenson · Jun 29th 2007

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

Sorry, here's the attachment...

Craig Ottley · Jun 29th 2007

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?

z

petehenson · Jun 29th 2007

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.

JF · Jun 30th 2007

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)

Code

Sub Copy1()
'
'
Dim tmp As Integer


tmp = Month(Now())
tmp2 = tmp + 5
'MsgBox (tmp)
'MsgBox (tmp2)


    Range("D70").Select
    Selection.Copy
    Cells(2, tmp2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("D71").Select
    Selection.Copy
    Cells(3, tmp2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("D72").Select
    Selection.Copy
    Cells(4, tmp2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


End Sub

Display More

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

Code

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

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

petehenson · Jun 30th 2007

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?

JF · Jun 30th 2007

Re: Copy & Paste Data Based On Current Month

The code can be shortend as follows:

Code

Sub Copy1() 
     '
     '
    Dim tmp As Integer 
     
    tmp = Month(Now()) 
    tmp2 = tmp + 5 
     'MsgBox (tmp)
     'MsgBox (tmp2)
     
     Range("D70:D72").Select 
    Selection.Copy 
    Cells(2, tmp2).Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 


End Sub

Display More

if you look at the code, the line

Code

tmp=Month(now())

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

Code

MsgBox(tmp)

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

Code

tmp2=tmp+5

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.

Copy & Paste Data Based On Current Month

Participate now!

Share

Users Viewing This Thread