Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional copy | Excel Discussion (Misc queries) | |||
Conditional copy | Excel Discussion (Misc queries) | |||
Conditional Copy | Excel Discussion (Misc queries) | |||
Conditional Copy | Excel Programming | |||
Conditional copy & paste | Excel Programming |