loop to copy paste

[Norvascom]

Hi,

I have a worksheet called "Sheet1" which has a template on range
A11:F20.
I would want to create a loop on a worksheet "Summary" (starting cell
B2) to copy paste several times the 'Sheet1" template range on
A11:F20. It would copy everytime below the prior. First time would be
on range A11:F20, then A21:F30, A31:F40...

It would stop to paste the template range until it reach all the
elements from a defined name range list (name range being "List").
Also at the same time that it paste, it would put the value of the
range list on the first row and first column of the pasted template.
As an example, on the first paste, it would be on cell B2, then B12...

Any help would be appreciated.
Thanks

 

[Tim Williams]

Best guess - some inconsistencies in your description.

'*******************************************************************************
Sub Tester()

Dim c As Range
Dim rngCopy As Range, rngPaste As Range

Set rngCopy = ThisWorkbook.Sheets("Sheet1").Range("A11:F20")
Set rngPaste = ThisWorkbook.Sheets("Sheet1").Range("B2")

For Each c In Range("List").Cells 'is this on a specific
worksheet?
rngCopy.Copy rngPaste
rngPaste.Value = c.Value
Set rngPaste = rngPaste.Offset(rngCopy.Rows.Count, 0)
Next c
End Sub
'*************************************************************************

Tim

 

[Norvascom]

Best guess - some inconsistencies in your description.

'**************************************************************************­*****
Sub Tester()

Dim c As Range
Dim rngCopy As Range, rngPaste As Range

    Set rngCopy = ThisWorkbook.Sheets("Sheet1").Range("A11:F20")
    Set rngPaste = ThisWorkbook.Sheets("Sheet1").Range("B2")

    For Each c In Range("List").Cells 'is this on a specific
worksheet?
        rngCopy.Copy rngPaste
        rngPaste.Value = c.Value
        Set rngPaste = rngPaste.Offset(rngCopy.Rows.Count, 0)
    Next c
End Sub
'*************************************************************************

Tim






- Show quoted text -

Thanks, it works as I expected. I just made a change to paste to a
different worksheet.

 

[Tim Williams (Theravance)]

Thanks, it works as I expected. I just made a change to paste to a
different worksheet.

Yes, sorry - my mistake when I wrote it...

Tim