Macro to Paste cell based on next cell selected

[djclifton]

Hi, I am a very basic user of VBA and was wondering if anyone with a little more experience might be able to help me with a solution to a problem that I have.

I have a function that I need to create a macro for that does the following;

1. Click on a cell in worksheet 'A'
2. Automatically display worksheet 'B'
3. Click on a cell in worksheet 'B'
4. Automatically return to worksheet 'A' and populate the cell originally clicked in worksheet 'A' with the contents of the cell clicked on in worksheet 'B'

Does anyone think that this multi step functionality is possible?

 

[Teeroy]

Hi and welcome to the forum.

Try the following macro with activation by Ctrl + Key (see Other ways to run a macro if you're unsure how to do this).

Sub Copy_data()
Dim rReturn As Range, rSelectRange As Range
Set rReturn = ActiveCell
Sheets("Sheet2").Activate 'change to the name that you require

On Error GoTo end_nice
Set rSelectRange = Application.InputBox("Select the range to copy", Type:=8)

If rSelectRange Is Nothing Then GoTo end_nice
If rSelectRange.Count <> 1 Then GoTo end_nice

rReturn.Value = rSelectRange.Value

end_nice:
    rReturn.Parent.Activate
End Sub

 

[Boller]

Perhaps this :

Put this in "Sheet1" module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("Sheet2").Activate
End Sub

Put this in "Sheet2" module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cel As Range
Set cel = ActiveCell
Sheets("Sheet1").Activate
ActiveCell = cel
End Sub

 

[djclifton]

Thanks Teeroy and Boller. Both macro's work perfectly.

Thanks a million