Application.Goto Reference (VBA problem when code repeated)

[omairhe]

here's my 1st code:-

Sub Macro2()
'
' Macro2 Macro
'

'
Selection.Copy
Sheets("Sheet2").Select
Application.Goto Reference:="R1C1"
Selection.End(xlToRight).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub



and i want to do the following on repeating this code:-



Sub Macro4()
'
' Macro4 Macro
'

'
Selection.Copy
Sheets("Sheet2").Select
Application.Goto Reference:="R2C1"
Selection.End(xlToRight).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

Only difference is in the Application.Goto Reference:="R2C1" in both these macro's


goto reference:='R1C1" in my 1st code is achieved by copying the 'cell range' which is 'A1' in (sheet1) and pasting it in the 'Name Box' of ("sheet2") which gives me R1C1 reference. The problem is that next time when i run this Macro on cell range 'A2' rather than 'A1' from ("Sheet1), and it pastes it in the name box of ("sheet2") just like i did in 1st code, it puts the same reference:="R1C1" as if it is copying A1 and not A2 in the name box ... i need was "R2C1" for 'A2' in the name box of (sheet2).

any help will be appreciated

 

[Andrew Poulsom]

Is this what you want?

Selection.Copy Worksheets("Sheet2").Range("A" & Selection.Row).End(xlToRight)

Generally you don't have to select Range objects to work with them.

 

[RoryA]

Could you explain in English what it is you are trying to achieve? That code looks horribly inefficient - for example you don't really do anything with Sheet2 so what's the point of that bit?

 

[omairhe]

actually its just an example of what i want....
name box refers to F4 btw
http://www.mrexcel.com/forum/showthread.php?t=387424
this is what my original post was about

 

[Andrew Poulsom]

Are you saying that Selection contains the name of a range that exists on Sheet2?

 

[omairhe]

Yes Sir... exactly ... and then paste that in Name Box ... pressing F4

 

[Andrew Poulsom]

So is it?

Selection.Copy Worksheets("Sheet2").Range(Selection.Value).End(xlToRight)

 

[omairhe]

yes kind of but instead of pasting it to the next sheet2 .. i want it to paste it in the 'Name Box' of sheet2... its on left side of formula bar...
thanks

 

[Andrew Poulsom]

Do you mean you want to Define the Name? How would you determine which cell it refers to?

 

[omairhe]

exactly... and its F5 command for name box not F4.. my mistake...

but u got it right

and from there i want to continue to rest of operations in my code