Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for copying and pasting values based on criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for copying and pasting values based on criteria
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 "Leigh Douglass" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for copying and pasting values based on criteria
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 "Otto Moehrbach" wrote: 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 "Leigh Douglass" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for copying and pasting values based on criteria
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 "Leigh Douglass" wrote in message ... 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 "Otto Moehrbach" wrote: 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 "Leigh Douglass" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying and pasting with a macro | Excel Discussion (Misc queries) | |||
Copying from one TAB to another based on certain criteria | New Users to Excel | |||
Copying across values based on criteria | Excel Programming | |||
Macro copying and pasting | Excel Discussion (Misc queries) | |||
Hided values appears when I'm copying/pasting it to other worksheet | Excel Discussion (Misc queries) |