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

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

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

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

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

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

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

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

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

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

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