Copying Cell address to another Cell

[Dean001]

Hi All,

Had loads of help so far and hopefully this is the last for a while...
I am copying values from one sheet to another, this cannot be automated as it is essentially random and carried out visually, but the bit I cant get to work is I also need to copy the cell address of the values I have copied. They need to be displayed in adjacent cells, rather than just using the Paste link option.
ie. the value needs to be copied to column D and the cell address needs to be copied to column C. I am using the below macro to copy the values (the best I have managed so far is to use the paste link function:

Selection.Copy
Sheets("DATA Dump").Select
Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste Link:=True
Sheets("data1").Select
Application.CutCopyMode = False
End Sub

Any help would be grately appreciated.
Thanks

 

[Dan_W]

Do you need the cells to be linked? If all your doing is copying the values from one sheet to another, it would be far quicker and easier to just assign the values to the destination cell(s) and avoid using the clipboard altogether. So, generally,

Sheets("Destination").Cells(1, 1).Value = Sheet("Source").Cells(1, 1).Value

Will instantly transfer the value in Cell A1 in the Worksheet "Source" to the corresponding Cell A1 in Worksheet Destination.

I don't know what the Selection you're copying in the above code snippet is, but I suppose your code could be written:

Sheets("DATA Dump").Cells(Rows.Count, "D").End(xlUp).Offset(1).Value = Selection.value

To put the address in the column to it's left (Column C), you could add:

Sheets("DATA Dump").Cells(Rows.Count, "D").End(xlUp).Offset(1, -1).Value = Selection.Address

Does that help?

 

[Fluff]

How about

   Dim Rng As Range
   
   Set Rng = Selection
   With Sheets("DATA Dump").Range("D" & Rows.Count).End(xlUp).Offset(1)
      .Value = Rng.Value
      .Offset(, -1).Value = Rng.Address
   End With

 

[Dean001]

Do you need the cells to be linked? If all your doing is copying the values from one sheet to another, it would be far quicker and easier to just assign the values to the destination cell(s) and avoid using the clipboard altogether. So, generally,

Sheets("Destination").Cells(1, 1).Value = Sheet("Source").Cells(1, 1).Value

Will instantly transfer the value in Cell A1 in the Worksheet "Source" to the corresponding Cell A1 in Worksheet Destination.

I don't know what the Selection you're copying in the above code snippet is, but I suppose your code could be written:

Sheets("DATA Dump").Cells(Rows.Count, "D").End(xlUp).Offset(1).Value = Selection.value

To put the address in the column to it's left (Column C), you could add:

Sheets("DATA Dump").Cells(Rows.Count, "D").End(xlUp).Offset(1, -1).Value = Selection.Address

Does that help?

Hi,

I dont need the cells to be linked, that was just the best I could do.
Where exactly would I add the above code?
would it need to look like this:

Selection.Copy
Sheets("DATA Dump").Select
Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Select
Sheets("DATA Dump").Cells(Rows.Count, "D").End(xlUp).Offset(1, -1).Value = Selection.Address
ActiveSheet.Paste Link:=True
Sheets("data1").Select
Application.CutCopyMode = False
End Sub

Thanks

 

[Dean001]

How about

   Dim Rng As Range
  
   Set Rng = Selection
   With Sheets("DATA Dump").Range("D" & Rows.Count).End(xlUp).Offset(1)
      .Value = Rng.Value
      .Offset(, -1).Value = Rng.Address
   End With

Hi,

Would that be a total replacement of the code or would I need to add it in?

Thanks

 

[Fluff]

That replaces everything you posted.

 

[Dean001]

That replaces everything you posted.

I just get an error when I paste that in as replacement

 

[Dean001]

I think I needed to be more specific as to where the address comes from, it needs to be from the original copy, so the $$ address in the image below

 

[Fluff]

I just get an error when I paste that in as replacement

What error?

 

[Dean001]

What error?

the error was my stupidity, I hadnt put end sub after it...
Thanks for your help