relative worksheet reference using range copy

[bdiddy]

Windows 7
Excel 2007
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am trying to make to relative worksheet reference within a range of cells while doing a copy of a template worksheet. The copy below is working ok when running my “Results worksheet” except reference cell to A11 or =SheetName(A1,TRUE) is not copying in the correct value from my sheets that have been copied from my template.
<o></o>
Here is the copy I am currently using in my for loop in “Results Summary”:<o></o>
ws.Range("A1:D12").Copy Destination:=Sheets("Results Summary").Cells(Rows.count, "A").End(xlUp).Offset(1)
count = count + 13
<o></o>
The function here is located in my template updates cell (A11) to be the active sheet name once the sheet has been copied.<o></o>
Function SheetName(rCell As Range, Optional UseAsRef As Boolean) As String
Application.Volatile
If UseAsRef = True Then
SheetName = "" & rCell.Parent.Name & ""
Else
SheetName = rCell.Parent.Name
End If
End Function
<o></o>
How do I update the ws.range to compensate for this? Currently every range instead of the newly copied sheet name it has “Results Summary” in the referenced cell.

Thank you for any help in advance!

 

[bdiddy]

Re: relative worksheet reference using range copy[SOLVED]

I worked a while on this. Here is what I needed in my for loop:

ws.Range("A1:D11").Copy
With Sheets("Results Summary").Cells(Rows.count, "A").End(xlUp).Offset(1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats