copy from one sheet to another?

February 3, 2010 at 01:02:43
Specs: Windows XP
Hi all,
Here's the scenario:
I have a data validation of names in cell a1
I have a data validation of locations in cell b1

If i select a name from a1 then a location from b1, I want excel to copy the NAME and put it into a cell on the worksheet with locations on it so that when I select say 'Jane Smyth' then 'Hospital', when I go to the Hospital worksheet, Jane Smyths name will be there in a certain cell.


See More: copy from one sheet to another?

Report •

February 3, 2010 at 07:26:49
Right Click the Sheet tab for the sheet with the dropwdowns.

Choose View code

Paste this code into the pane that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$B$1" Then
  Sheets(Target.Text).Range("A1").PasteSpecial (xlPasteValues)
  Application.CutCopyMode = False
 End If
End Sub

When B1 is changed via the dropdown, A1 will be copied to A1 of the page whose name appears in B1.

As written, if you pick a name that should be copied to Hospital and Hospital is already displayed in B1, it will not automatically copy A1. You would need to pull down B1 and select Hospital again.

If that is an issue, then come on back with more specifics and we'll see what we can do.

Report •
Related Solutions

Ask Question