relative worksheet reference using range copy

bdiddy

New Member
Joined
Jan 25, 2011
Messages
11
Windows 7
Excel 2007
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
Here is the copy I am currently using in my for loop in “Results Summary”:<o:p></o:p>
ws.Range("A1:D12").Copy Destination:=Sheets("Results Summary").Cells(Rows.count, "A").End(xlUp).Offset(1)
count = count + 13
<o:p></o:p>
The function here is located in my template updates cell (A11) to be the active sheet name once the sheet has been copied.<o:p></o:p>
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:p></o:p>
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!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

bdiddy

New Member
Joined
Jan 25, 2011
Messages
11
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
 

Forum statistics

Threads
1,136,969
Messages
5,678,890
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top