When is data removed from the clipboard? Can I avoid copying a range for each paste?

[EirikDaude]

I was originally trying to do something like this:

Set kopierFra = Range(ws.Range("B1048576").End(xlUp).Offset(0, -1), ws.Range("B1048576").End(xlUp).Offset(0, 5))
kopierFra.Copy
For i = 1 To CLng(ufKopierRad.tbxAntalKopiar)
Set kopierTil = kopierFra.Offset(0, i)
kopierTil.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
kopierTil.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
kopierTil.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next
But it seems the range I want to copy is removed from the clipboard for every paste-operation I do? Is there any way to avoid this, or will I simply have to put a copy-statement before every paste? Or is there alternately some way to paste all of the properties I want from the source-range to the destination without needing three paste-statements, or even a for-loop?

[snb]

Simply use 'copy' to copy 'all'.

[EirikDaude]

Ah, right, I didn't think that one brought along stuff like validation and comments :P I guess I should have read a bit more of the documentation :) Thanks for your help, at any rate :)

I guess that means that this is the most efficient way to do what I want to do?

Set kopierFra = Range(ws.Range("B1048576").End(xlUp).Offset(0, -1), ws.Range("B1048576").End(xlUp).Offset(0, 5))

For i = 1 To CLng(ufKopierRad.tbxAntalKopiar)
Set kopierTil = kopierFra.Offset(i, 0)
kopierFra.Copy Destination:=kopierTil
Next

[snb]

I don't think so:


with ws.cells(rows.count,2).End(xlUp).Offset(, -1)
.resize(,6).copy .Offset(1).resize(ufKopierRad.tbxAntalKopiar,6)
end with

[EirikDaude]

Thanks again :)