Solved How to paste to a Cell address

December 19, 2016 at 08:43:37
Specs: Windows 64
' copies and pastes to relevant cell
    Sheets("ADMIN").Select
      Sheets("ADMIN").Range("N29").Copy
      
    With Sheets("LEGEND").Range(("ADMIN").Range("N36"))
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
    End With

Hi,

Range(("ADMIN").Range("N36"))
this cell N36 finds the address of a cell in another worksheet. I would like to use that cell address , lets say the result is (Sheet2!AT5) and paste ADMIN!N29 to that cell. Now I know the above code is wrong and I need help to make it right

Regards
Tony


See More: How to paste to a Cell address

Report •

#1
December 19, 2016 at 08:45:39
=CELL("Address",INDEX(LEGEND!$AT$2:$AT$49,MATCH($N$27,LEGEND!$AP$2:$AP$49,0)))

This is the formula I have used to ascertain the cell address eg (Sheet2!AT5)


Report •

#2
December 20, 2016 at 13:01:46
✔ Best Answer
Hi Guys,
I know I have had no responses on this, and I never expect to get all my answers. As a matter of course I have been working on this and have come up with a solution that works very well

<preSub addamendemail()

Application.ScreenUpdating = False

If MsgBox("DO YOU WISH TO ADD / AMEND THIS PLAYERS E-MAIL ADDRESS?", vbYesNo) = vbYes Then

Sheets("LEGEND").Visible = True
Sheets("LEGEND").Select
Sheets("LEGEND").Unprotect Password:="2L4NTJEzNuKn"

Dim row As Integer
' first row of column list to add information to
row = 2
' ascertains column to where information is required to be posted
Do While (Sheets("LEGEND").Range("AP" & row).Value <> "")
' loops through column (legend) until it matches the criteria (admin)to determine
' the row where the information is to be posted
' Then posts into the column (Legend!AT) the information in (Admin!R27)
If Sheets("LEGEND").Range("AP" & row).Value = Sheets("ADMIN").Range("N27").Value Then
Sheets("LEGEND").Range("AT" & row).Value = Sheets("ADMIN").Range("R27").Value
End If
' add 1 to the row to loop through until (Admin!27) is located
row = row + 1

Loop


Sheets("LEGEND").Protect Password:="2L4NTJEzNuKn", DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("LEGEND").Visible = False


Sheets("ADMIN").Range("R27").Select
Sheets("ADMIN").Range("R27").Clearcontents

MsgBox "PLAYER'S E-MAIL ADDRESS HAS BEEN ADDED/AMENDED", vbInformation

End If

Application.ScreenUpdating = True

End Sub>


Report •
Related Solutions


Ask Question