Macro that uses a value as the range

[jpbat1591]

Hi,

I am wondering if anyone could shed some light on how i can program a value
say in A1 that is constantly changing, and use the value as the destination
to paste information on another sheet.

 

[JLGWhiz]

This sets c as the object variable for a value in sheet 2 that equals the
value in A1 of sheet 1. It then copies someRange designated by the user to a
destination that is two cells to the right of the cell with the found value
of c.

Sub findValueA1()
Dim c As Range
Set someRange = Sheets(1).Range("C5") '<<<Change to need
Set c = Sheets(2).Cells.Find(Sheets(1).Range("A1").Value _
LookIn:=xlValues) '<<<add additional parameters in needed.
If Not c Is Nothing Then
Sheets(1).Range(someRange).Copy Sheets(2) _
c.Offset(0, 2)
End If
End Sub

 

[Gary''s Student]

Say we want to put a message in Sheet2
Sheet1, cell A1 contains Z100 (the destination address)
Sheet2, cell A2 contains the message:

"Klaatu barada nikto"


Here is a sample macro:

Sub gort()
Set a1 = Range("A1")
Set a2 = Range("A2")
destin = a1.Value
a2.Copy Sheets("Sheet2").Range(destin)
End Sub