Caopying and Pasting While Retaining the Original Cell Reference

[DRJD]

Just a quick one, I hope but I can't seem to quite get a grasp on this one.

What I want to do is populate a worksheet with tables, based on how many elements there are in a particular array.

For example, say I have an array ary("Title 1", "Title 2", "Title 3", "Title 4", "Title 5") I would like to copy the table in worksheet "Original" (see attached) four times, and arrange the total of 5 tables in two columns, as shown in worksheet "Desired".

What I can't seem to work out is how to maintain the reference to the original table (which will always be in the range) and paste into the four other ranges as necessary.

The number of elements in the array changes on a case by case basis, so I can't just hard code the references. There can be anything from 1 to 15 elements in the array, so I need i to be flexible.

Has anybody got any pointers on this one?

[Bob Phillips]

Public Sub CopyTable()
Dim SourceRng As Range

With ActiveSheet

Set SourceRng = .Range("B1:D20")
SourceRng.Copy .Range("G1")
.Range("G1").Value = "Title 2"
SourceRng.Copy .Range("B22")
.Range("G1").Value = "Title 3"
SourceRng.Copy .Range("G22")
.Range("G1").Value = "Title 4"
SourceRng.Copy .Range("G1")
.Range("B43").Value = "Title 5"
.Columns(7).ColumnWidth = .Columns(2).ColumnWidth
.Columns(8).ColumnWidth = .Columns(3).ColumnWidth
.Columns(9).ColumnWidth = .Columns(4).ColumnWidth

End With
End Sub

[DRJD]

Thank you for your help xld, but unfortunately the above code does not qiuite do what I want it to do as it will only work for the example I have given. In the template I am constructing, the number of elements in the array can vary - there may be any number from 1 to about 15, so I can't have the references hard-coded in the VBA. Instead I nedd some sort of relative reference, which will depend on how many elements there are in the array.

I suppose if I kept the "source range" and added a "current range" to keep track of where the active cell is, then I may be able to make it work.

Is that going to be the easiest way to do it?

Thanks

Dave

[Bob Phillips]

Public Sub CopyTable()
Dim SourceRng As Range
Dim NumCopies As Variant
Dim i As Long, j As Long

With ActiveSheet

NumCopies = InputBox("Copy how many times?")
If NumCopies <> "" Then

Set SourceRng = .Range("B1:D20")
For i = 1 To NumCopies

For j = 1 To 2

If i <= NumCopies Then

SourceRng.Copy .Cells(((i - 1) \ 2) * 21 + 1, (j - 1) * 5 + 2)
.Cells(((i - 1) \ 2) * 21 + 1, (j - 1) * 5 + 2).Value = "Title " & i
i = i + 1
End If
Next j
If i <= NumCopies Then i = i - 1
Next i
.Columns(7).ColumnWidth = .Columns(2).ColumnWidth
.Columns(8).ColumnWidth = .Columns(3).ColumnWidth
.Columns(9).ColumnWidth = .Columns(4).ColumnWidth
End If
End With
End Sub

[DRJD]

Thanks for that.

One final question (hopefully): If I was to want to change the source range so that I could reuse this macro in different templates, how would that affect the "If i <= NumCopies" loop?

Say, for example, the source range is B48:D67 instead of B1:D20, how would I go about producing the same results relative to the position of the Source Range (please see attached "Table Example 2" for desired output).

Thanks again for all your help.

[DRJD]

I've answered my own question since posting the above, so please ignore my last quastion.

Thanks again for all your help.