PDA

View Full Version : Paste Named Range



LOSS1574
01-22-2009, 12:32 PM
I'm using the below code to copy/paste range into a worksheet.

I'm trying to modify the code so that it will paste the range items in Column ("A3") until ("A80") and then skips a column ("F") and continues entering the range items again in column ("G3") until ("G80"). Ditto for that column, skips column ("L") and continues entering range items in column ("M3") until ("M80")

Select Case FListboxMover.PosList.Value
Case "TOP200"
Range("Heading1").Copy Range("A1")
Range("TOP201").Copy Range("A3")


Thanks,

mdmackillop
01-22-2009, 03:41 PM
it will paste the range items in Column ("A3") until ("A80") and then skips a column ("F")
Please clarify. Is the source a single column or block of cells? Do you mean A3:E80 then skip F etc.? Does the source have the same number of rows (3-80)?

mdmackillop
01-22-2009, 04:13 PM
I thought this should insert data in the green cells, but it enters thee data in Column A. Anyone got a reason why?

david000
01-22-2009, 05:26 PM
I see what your saying but i think that there is no areas collection to be had unless the cells are not touching...thus the sourse data could be achieved with a clever range name then accessing the areas of touching cells. I find that a Excel bug personally.

Sub test()
Dim Source As Range
Dim Tgt As Range
Dim i As Long
Set Source = Sheets(1).Cells(1, 1).CurrentRegion
With Sheets(2)
Set Tgt = .Cells(3, 1)
For i = 1 To 11 Step 2
Set Tgt = Union(Tgt, Range(.Cells(3, i), .Cells(10, i)))
Next
Tgt.Interior.ColorIndex = 35
'MsgBox Tgt.Address
For i = 1 To Tgt.Areas.Count
Tgt.Areas(i).Value = Source.Value '<<<<< here I see what you mean.
Next i
End With
End Sub

david000
01-23-2009, 06:07 PM
Like this mdmackillop, but I still don't think I understand the original question.

Sub test2()
Dim Source As Range
Dim Tgt As Range
Dim i As Long
'Set Source = Sheets(1).Cells(1, 1).CurrentRegion
With Sheets(2)
Set Tgt = .Cells(3, 1)
For i = 1 To 10 Step 2
Set Tgt = Union(Tgt, Range(.Cells(3, i), .Cells(15, i)))
Next
Tgt.Interior.ColorIndex = 35
'MsgBox Tgt.Address
For i = 1 To Tgt.Areas.Count
Tgt.Areas(i).Value = Range("Source").Areas(i).Value '<<<<< here I see what you mean.
Next i
End With
End Sub