Macro for copying and pasting values based on criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leigh Douglass - 28 Jan 2008 10:44 GMT

Hi

I need to write/record a macro to copy and paste special values of certain
columns depending on the column heading.

I have a table of data with headings P1 YTD, P2 YTD, etc up to 12. Some
columns are vlookups from other sheets. I would like a macro to copy and
paste the values of the columns as I move into a different period. So as I
enter P3 YTD, i would like my macro to copy P2 YTD columns and paste the
values and so next month it would need to do the sam,e with P3 YTD.

Any help gratefully received.

Leigh Douglass

Reply to this Message

Otto Moehrbach - 28 Jan 2008 12:12 GMT

Leigh
What you want is pretty easy to do but more information is needed from
you.
You say "I would like a macro to copy and paste the values of the columns as
I move into a different period."
What do you mean by "move into a different period"? Exactly what do you
want to do to make this macro run? Click on some cell? In a specific
column? Maybe click on the header cell of a specific column? Do you want
the macro to run each and every time you click on the same cell, or do you
want the macro to check if some criteria exists, or doesn't exist, and run
or not run depending on the criteria? Or maybe all this depends on the
month changing?
You say "as I enter P3 YTD.....". Exactly what does "enter" mean in this
context?
Please post back with this information and you'll get plenty of help. HTH
Otto

> Hi
>
[quoted text clipped - 11 lines]
>
> Leigh Douglass

Reply to this Message

Leigh Douglass - 28 Jan 2008 12:26 GMT

Hi Otto

Sorry for my lack of details. I have 12 column headings P1 YTD through to
P12 YTD, these refer to the month in the year. For my current month,
currently P9 YTD, the column has a vlookup to populate the data. I have a
cell where I enter the current period, which is part of another formula.
When i change that cell to P10 YTD, I would like a macro to copy the vlookup
and paste the values for the column heading P9 YTD, and when i change the
cell to P11 YTD, i would like the macro to copy and paste the P10 YTD
heading, etc. I have all my vlookups in place for all periods, but once a
period has ended the data never changes so i no longer need to keep the
lookup. Thanks

> Leigh
> What you want is pretty easy to do but more information is needed from
[quoted text clipped - 27 lines]
> >
> > Leigh Douglass

Reply to this Message

Otto Moehrbach - 28 Jan 2008 16:34 GMT

Leigh
This little macro will do what you want. I assumed your headers are in
row 1 starting with A1. When you enter anything in any of the cells in
B1:L1, this macro will copy and paste values, the column to the left of the
cell that changed. Make the necessary changes to fit with your actual data
layout. Note that this macro will not react if you make an entry in A1.
This is because if it did react, it would be looking for a column to the
left of Column A and that would produce an error.
This macro is a sheet event macro and must be placed in the sheet module for
the sheet in question. To access that module, right-click on the sheet tab
and select View Code. Paste this macro into that module. "X" out of the
module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("B1:L1")) Is Nothing Then
Range(Target.Offset(1, -1), Cells(Rows.Count,
Target.Offset(, -1).Column).End(xlUp)).Copy
Target.Offset(1, -1).PasteSpecial xlPasteValues
End If
Application.CutCopyMode = False
End Sub

> Hi Otto
>
[quoted text clipped - 52 lines]
>> >
>> > Leigh Douglass

Reply to this Message