Conditional copy
 

I have a range E5:H72 used as a data table. It is updated with a new column
of information weekly Next week it will be E5:I72 and so on. I want to take
the last 13 columns (weeks) and copy paste them to another worksheet for
reporting.

Can anyone help me with this, noting that:
when there are less than 13 columns I want them all
when there are greaterte than 13 I want the 13 rightmost (most recent)
columns

Thanks

Conditional copy
 

this may be a bit of a simplistic example, but use F8 to step through
it...ask again if there's something unclear:

Option Explicit
Sub copyCols()
Dim source As Range
Dim target As Range
Dim lastCol As Long
Dim firstCol As Long

With Worksheets("Sheet1")
lastCol = .Range("E5").End(xlToRight).Column
firstCol = 5
If lastCol 17 Then firstCol = lastCol - 12
Set source = .Range(.Cells(5, firstCol), .Cells(72, lastCol))
End With
Set target = Worksheets("sheet2").Range("B2")
With source
target.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub



"Buzz" wrote:

I have a range E5:H72 used as a data table. It is updated with a new column
of information weekly Next week it will be E5:I72 and so on. I want to take
the last 13 columns (weeks) and copy paste them to another worksheet for
reporting.

Can anyone help me with this, noting that:
when there are less than 13 columns I want them all
when there are greaterte than 13 I want the 13 rightmost (most recent)
columns

Thanks

Conditional copy
 

Thanks very much Patrick. That worked well.
If you had time, could you teach me a bit by illuminating these parts of
your code? If you do not have time, don't bother. I am most appreciative of
your help and the solution you provided.

My questions (only if you have time):
I am puzzled by "Range(.Cells(5, firstCol), .Cells(72, lastCol))" because
the cells are identified by row - column; I thought they needed to be
identified as RC.

And I am puzzled by the lack of a "copy" and "paste" statement. Does the
"With source
target.resize....
End with"

accomplish the paste after material to be copied is identified in the
previous "With Worksheets....End With" statement?

Thanks again very much for your help.




"Patrick Molloy" wrote:

this may be a bit of a simplistic example, but use F8 to step through
it...ask again if there's something unclear:

Option Explicit
Sub copyCols()
Dim source As Range
Dim target As Range
Dim lastCol As Long
Dim firstCol As Long

With Worksheets("Sheet1")
lastCol = .Range("E5").End(xlToRight).Column
firstCol = 5
If lastCol 17 Then firstCol = lastCol - 12
Set source = .Range(.Cells(5, firstCol), .Cells(72, lastCol))
End With
Set target = Worksheets("sheet2").Range("B2")
With source
target.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub



"Buzz" wrote:

I have a range E5:H72 used as a data table. It is updated with a new column
of information weekly Next week it will be E5:I72 and so on. I want to take
the last 13 columns (weeks) and copy paste them to another worksheet for
reporting.

Can anyone help me with this, noting that:
when there are less than 13 columns I want them all
when there are greaterte than 13 I want the 13 rightmost (most recent)
columns

Thanks

Conditional copy
 

Patrick

Good to hear from you.

What ya been up to?


Gord

On Tue, 10 Feb 2009 09:06:01 -0800, Patrick Molloy
wrote:

this may be a bit of a simplistic example, but use F8 to step through
it...ask again if there's something unclear:

Option Explicit
Sub copyCols()
Dim source As Range
Dim target As Range
Dim lastCol As Long
Dim firstCol As Long

With Worksheets("Sheet1")
lastCol = .Range("E5").End(xlToRight).Column
firstCol = 5
If lastCol 17 Then firstCol = lastCol - 12
Set source = .Range(.Cells(5, firstCol), .Cells(72, lastCol))
End With
Set target = Worksheets("sheet2").Range("B2")
With source
target.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub



"Buzz" wrote:

I have a range E5:H72 used as a data table. It is updated with a new column
of information weekly Next week it will be E5:I72 and so on. I want to take
the last 13 columns (weeks) and copy paste them to another worksheet for
reporting.

Can anyone help me with this, noting that:
when there are less than 13 columns I want them all
when there are greaterte than 13 I want the 13 rightmost (most recent)
columns

Thanks