Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
I want to be able to create a macro where if you click a button it pastes
values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
highlight range of cells you want copies. Then run macro. macro will ask the
new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
How could I change the code to paste special-values and to always output to
column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
just hard code
MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
Ok, but it ends up the code pastes only the highlighted cell, and I need that
cell plus the next 17 cells in the column. Please advise, Adam Bush "Joel" wrote: just hard code MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
Highlight all cells you want copied. Use the code below. Same code as
before except the harrd coded column "C" Sub CopyColumns() MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Ok, but it ends up the code pastes only the highlighted cell, and I need that cell plus the next 17 cells in the column. Please advise, Adam Bush "Joel" wrote: just hard code MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
I've been using the code for 5 columns of info (1 week) with about 18 rows
per column. When I highlight the 5X18 area and activate the code, the only thing that gets copied is the first row that I highlighted. Any ideas? Thanks Adam Bush "Joel" wrote: Highlight all cells you want copied. Use the code below. Same code as before except the harrd coded column "C" Sub CopyColumns() MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Ok, but it ends up the code pastes only the highlighted cell, and I need that cell plus the next 17 cells in the column. Please advise, Adam Bush "Joel" wrote: just hard code MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
There are blank row between the datta.
" wrote: I've been using the code for 5 columns of info (1 week) with about 18 rows per column. When I highlight the 5X18 area and activate the code, the only thing that gets copied is the first row that I highlighted. Any ideas? Thanks Adam Bush "Joel" wrote: Highlight all cells you want copied. Use the code below. Same code as before except the harrd coded column "C" Sub CopyColumns() MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Ok, but it ends up the code pastes only the highlighted cell, and I need that cell plus the next 17 cells in the column. Please advise, Adam Bush "Joel" wrote: just hard code MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
Yes that is correct. I was hoping the code would check only the first row of
data for a zero, and if there isn't one would copy over the next 18 rows also. Is this possible? "Joel" wrote: There are blank row between the datta. " wrote: I've been using the code for 5 columns of info (1 week) with about 18 rows per column. When I highlight the 5X18 area and activate the code, the only thing that gets copied is the first row that I highlighted. Any ideas? Thanks Adam Bush "Joel" wrote: Highlight all cells you want copied. Use the code below. Same code as before except the harrd coded column "C" Sub CopyColumns() MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Ok, but it ends up the code pastes only the highlighted cell, and I need that cell plus the next 17 cells in the column. Please advise, Adam Bush "Joel" wrote: just hard code MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
Sub CopyColumns()
'MyColumn = InputBox("Enter Destination Column") MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row LastCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column Set NewRange = Range(ActiveCell, Cells(LastRow, LastCol)) For Each cell In NewRange If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Yes that is correct. I was hoping the code would check only the first row of data for a zero, and if there isn't one would copy over the next 18 rows also. Is this possible? "Joel" wrote: There are blank row between the datta. " wrote: I've been using the code for 5 columns of info (1 week) with about 18 rows per column. When I highlight the 5X18 area and activate the code, the only thing that gets copied is the first row that I highlighted. Any ideas? Thanks Adam Bush "Joel" wrote: Highlight all cells you want copied. Use the code below. Same code as before except the harrd coded column "C" Sub CopyColumns() MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Ok, but it ends up the code pastes only the highlighted cell, and I need that cell plus the next 17 cells in the column. Please advise, Adam Bush "Joel" wrote: just hard code MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special If Statement
Thanks for your help Joel
"Joel" wrote: Sub CopyColumns() 'MyColumn = InputBox("Enter Destination Column") MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row LastCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column Set NewRange = Range(ActiveCell, Cells(LastRow, LastCol)) For Each cell In NewRange If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Yes that is correct. I was hoping the code would check only the first row of data for a zero, and if there isn't one would copy over the next 18 rows also. Is this possible? "Joel" wrote: There are blank row between the datta. " wrote: I've been using the code for 5 columns of info (1 week) with about 18 rows per column. When I highlight the 5X18 area and activate the code, the only thing that gets copied is the first row that I highlighted. Any ideas? Thanks Adam Bush "Joel" wrote: Highlight all cells you want copied. Use the code below. Same code as before except the harrd coded column "C" Sub CopyColumns() MyColumn = "C" Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: Ok, but it ends up the code pastes only the highlighted cell, and I need that cell plus the next 17 cells in the column. Please advise, Adam Bush "Joel" wrote: just hard code MyColumn = "C" " wrote: How could I change the code to paste special-values and to always output to column C? Thanks and I appreciate your help, Adam Bush "Joel" wrote: highlight range of cells you want copies. Then run macro. macro will ask the new column for the data. Sub CopyColumns() MyColumn = InputBox("Enter Destination Column") Set cell = Range(MyColumn & "1") ColumnNumber = cell.Column MyColumnOffset = ColumnNumber - ActiveCell.Column For Each cell In ActiveCell.CurrentRegion If cell < 0 Then cell.Offset(0, MyColumnOffset) = cell End If Next cell End Sub " wrote: I want to be able to create a macro where if you click a button it pastes values from say column Q to column C. However, I only want it to paste the info. over if the number in the first row is not zero. If it is zero, the macro will do nothing. Every data set I am looking at will have five columns to paste per week, about 18 rows per column. The weeks are arranged vertically so one would be from C1 to G 18 and the next would be from C24 to G41. It would be great if I could use just one button but I'd settle for using one per week. Thanks and I'd appreciate and Ideas, Adam Bush |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
How do I capture user paste action and convert to Paste Special | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |