CODEWITHSUNDEEP
excelvba
James
James

Reputation: 21

Copying cell references to another column vba

Trying to copy cell references from one column to another (ideally adding "PR" on the end of the string but i haven't gotten that far), my attempt is below. I cant find a solution online so I am sorry if this is a repeat !

please let me know how to correct my attempt 

 Sub copynames()
 Dim source as range
 Dim target as range
 Dim n as Name

set source = range("A1:A3")
set target = range("b1:b3")

for i=1 to source.Rows.Count
 target.cell(i,0).Names = source.Cell(i,0).Names
next i

End Sub

Upvotes: 0

Views: 50

Answers (2)

Wedge
Wedge

Reputation: 1826

I'm assuming by "cell reference" you are talking about Names. They don't exist as part of a specific range, they're a collection of references in the workbook (what you see when you look at the Name Manager). So you need to access that collection to create a new name. I believe you want to name a range based of the names referring to an existing range, this code should do that.

Sub copynames()
Dim source As Range
Dim target As Range

Set source = Range("A1:A3")
Set target = Range("b1:b3")

'Set the target cells to the name of the source cells with "PR" appended
For i = 1 To source.Rows.Count
 ThisWorkbook.Names.Add source(i, 1).Name.Name & "PR", target(i, 1)
Next i

End Sub

Upvotes: 1

William
William

Reputation: 997

If I understand your question correctly, you are looking to give each cell in your target range the same formula/cell reference as the corresponding source cell. If so, this should do what you need:

Sub copyNames()

Dim source As Range
Dim target As Range

Set source = Range("A1:A3")
Set target = Range("B1:B3")

For i = 1 To source.Rows.Count
 target.Cells(i, 1).Formula = source.Cells(i, 1).Formula
Next i

End Sub

If you need to append "PR" to the end of the values which will be displayed in the target cells, you will need to add this text to the values in the referenced cells which the target cells will be pointing to.

Alternatively you could just copy the values from the source data instead and append your text to that, if a live reference is not important (change the "Formula" property to "Value").

Upvotes: 0

Related Questions