title: vba finding something in one worksheet and copying value to same location in another worksheet
body: <p>This is driving me insane, I have been at this for 2 weeks now and I am getting nowhere and this is only the first part of quite a complicated( for me) project. I have 1 workbook with 3 sheets (availability)(allocation)(final). I collect peoples availability then I am hoping to perform a series of tasks. column (a) has a list of peoples names ( this is not fixed and could expand and contract) the headings of the other columns are dates ( this is not fixed and could expand and contract). First I need to search through all the columns ( in availability) and find an instance of (x1) record the name of the person and the address of the cell where (x1) was found. This I have done and using ( immediate) I can see that the name and cell location is being found. the next bit is to open the allocation sheet find the persons name and then put (x1) in the same cell address that it was found in. I have added the code to activate the (allocation) worksheets and set the range where I want the search for the name to begin, however when I run the code it gets to line  "Worksheets("allocation").Activate" and gives the error "Application-defined or object-defined error". I have no idea why. You will see from the code that I am a real beginner with VBA as I am sure there is a much more  efficient way of writing this code, however this is what I have come up with after 2 weeks of pains taking searching. If you are able to help with this and or suggest any improvements could I ask that you please comment any code so I know what it is doing otherwise I am never going to be able to work out what is going on and learn anything.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  'Dim namerange As Range
  'Dim namecell As Range
  'Dim firstcell As String
  'Dim singlecell As Range
  Dim listofcellsb As Range
  Dim listofcellsc As Range
  Dim listofcellsd As Range
  Dim listofcellse As Range
  Dim listofcellsf As Range
  Dim listofcellsg As Range
  Dim listofcellsh As Range
  Dim listofcellsi As Range
  Dim listofcellsj As Range
  Dim listofcellsk As Range
  Dim listofcellsl As Range
  Dim listofcellsm As Range
  Dim listofcellsn As Range
  Dim listofcellso As Range
  Dim listofcellsp As Range
  Dim listofcellsq As Range
  Dim listofcellsr As Range
  Dim listofcellss As Range
  Dim listofcellst As Range
  Dim listofcellsu As Range
  Dim listofcellsv As Range
  Dim listofcellsw As Range
  Dim listofcellsx As Range
  Dim listofcellsy As Range
  Dim listofcellsz As Range
  Dim addresscell As String
  Dim namecell As String
  
  
  
  
  Set listofcellsb = Range("b4", Range("b3").End(xlDown))
  
  Worksheets("allocation").Activate
  Range("a3").Select
  
      For Each singlecellb In listofcellsb
          If singlecellb.Value = "x1" Then
              Debug.Print singlecellb.Offset(0, 0).Address
              ActiveCell.Value = singlecellb.Offset(0, -1).Value
          End If
      Next singlecellb
  Worksheets("availability").Activate
  
  Set listofcellsc = Range("c4", Range("c3").End(xlDown))
      For Each singlecellc In listofcellsc
          If singlecellc.Value = "x1" Then
              Debug.Print singlecellc.Offset(0, 0).Address
              Debug.Print singlecellc.Offset(0, -2).Value
          End If
      Next singlecellc
  
  Set listofcellsd = Range("d4", Range("d3").End(xlDown))
      For Each singlecelld In listofcellsd
          If singlecelld.Value = "x1" Then
              Debug.Print singlecelld.Offset(0, 0).Address
              Debug.Print singlecelld.Offset(0, -3).Value
          End If
      Next singlecelld
  
  Set listofcellse = Range("e4", Range("e3").End(xlDown))
      For Each singlecelle In listofcellse
          If singlecelle.Value = "x1" Then
              Debug.Print singlecelle.Offset(0, 0).Address
              Debug.Print singlecelle.Offset(0, -4).Value
          End If
      Next singlecelle
  
  Set listofcellsf = Range("f4", Range("f3").End(xlDown))
      For Each singlecellf In listofcellsf
          If singlecellf.Value = "x1" Then
              Debug.Print singlecellf.Offset(0, 0).Address
              Debug.Print singlecellf.Offset(0, -5).Value
          End If
      Next singlecellf
  
  Set listofcellsg = Range("g4", Range("g3").End(xlDown))
      For Each singlecellg In listofcellsg
          If singlecellg.Value = "x1" Then
              Debug.Print singlecellg.Offset(0, 0).Address
              Debug.Print singlecellg.Offset(0, -6).Value
          End If
      Next singlecellg
  
  Set listofcellsh = Range("h4", Range("h3").End(xlDown))
      For Each singlecellh In listofcellsh
          If singlecellh.Value = "x1" Then
              Debug.Print singlecellh.Offset(0, 0).Address
              Debug.Print singlecellh.Offset(0, -7).Value
          End If
      Next singlecellh
  
  Set listofcellsi = Range("i4", Range("i3").End(xlDown))
      For Each singlecelli In listofcellsi
          If singlecelli.Value = "x1" Then
              Debug.Print singlecelli.Offset(0, 0).Address
              Debug.Print singlecelli.Offset(0, -8).Value
          End If
      Next singlecelli
  
  Set listofcellsj = Range("j4", Range("j3").End(xlDown))
      For Each singlecellj In listofcellsj
          If singlecellj.Value = "x1" Then
              Debug.Print singlecellj.Offset(0, 0).Address
              Debug.Print singlecellj.Offset(0, -9).Value
          End If
      Next singlecellj
  
  Set listofcellsk = Range("k4", Range("k3").End(xlDown))
      For Each singlecellk In listofcellsk
          If singlecellk.Value = "x1" Then
              Debug.Print singlecellk.Offset(0, 0).Address
              Debug.Print singlecellk.Offset(0, -10).Value
          End If
      Next singlecellk
  
  Set listofcellsl = Range("l4", Range("l3").End(xlDown))
      For Each singlecelll In listofcellsl
          If singlecelll.Value = "x1" Then
              Debug.Print singlecelll.Offset(0, 0).Address
              Debug.Print singlecelll.Offset(0, -11).Value
          End If
      Next singlecelll
  
  Set listofcellsm = Range("m4", Range("m3").End(xlDown))
      For Each singlecellm In listofcellsm
          If singlecellm.Value = "x1" Then
              Debug.Print singlecellm.Offset(0, 0).Address
              Debug.Print singlecellm.Offset(0, -12).Value
          End If
      Next singlecellm
  
  Set listofcellsn = Range("n4", Range("n3").End(xlDown))
      For Each singlecelln In listofcellsn
          If singlecelln.Value = "x1" Then
              Debug.Print singlecelln.Offset(0, 0).Address
              Debug.Print singlecelln.Offset(0, -13).Value
          End If
      Next singlecelln
  
  Set listofcellso = Range("o4", Range("o3").End(xlDown))
      For Each singlecello In listofcellso
          If singlecello.Value = "x1" Then
              Debug.Print singlecello.Offset(0, 0).Address
              Debug.Print singlecello.Offset(0, -14).Value
          End If
      Next singlecello
  
  Set listofcellsp = Range("p4", Range("p3").End(xlDown))
      For Each singlecellp In listofcellsp
          If singlecellp.Value = "x1" Then
              Debug.Print singlecellp.Offset(0, 0).Address
              Debug.Print singlecellp.Offset(0, -15).Value
          End If
      Next singlecellp
  
  Set listofcellsq = Range("q4", Range("q3").End(xlDown))
      For Each singlecellq In listofcellsq
          If singlecellq.Value = "x1" Then
              Debug.Print singlecellq.Offset(0, 0).Address
              Debug.Print singlecellq.Offset(0, -16).Value
          End If
      Next singlecellq
  
  Set listofcellsr = Range("r4", Range("r3").End(xlDown))
      For Each singlecellr In listofcellsr
          If singlecellr.Value = "x1" Then
              Debug.Print singlecellr.Offset(0, 0).Address
              Debug.Print singlecellr.Offset(0, -17).Value
          End If
      Next singlecellr
  
  Set listofcellss = Range("s4", Range("s3").End(xlDown))
      For Each singlecells In listofcellss
          If singlecells.Value = "x1" Then
              Debug.Print singlecells.Offset(0, 0).Address
              Debug.Print singlecells.Offset(0, -18).Value
          End If
      Next singlecells
  
  Set listofcellst = Range("t4", Range("t3").End(xlDown))
      For Each singlecellt In listofcellst
          If singlecellt.Value = "x1" Then
              Debug.Print singlecellt.Offset(0, 0).Address
              Debug.Print singlecellt.Offset(0, -19).Value
          End If
      Next singlecellt
  
  Set listofcellsu = Range("u4", Range("u3").End(xlDown))
      For Each singlecellu In listofcellsu
          If singlecellu.Value = "x1" Then
              Debug.Print singlecellu.Offset(0, 0).Address
              Debug.Print singlecellu.Offset(0, -20).Value
          End If
      Next singlecellu
  
  Set listofcellsv = Range("v4", Range("v3").End(xlDown))
      For Each singlecellv In listofcellsv
          If singlecellv.Value = "x1" Then
              Debug.Print singlecellv.Offset(0, 0).Address
              Debug.Print singlecellv.Offset(0, -21).Value
          End If
      Next singlecellv
  
  Set listofcellsw = Range("w4", Range("w3").End(xlDown))
      For Each singlecellw In listofcellsw
          If singlecellw.Value = "x1" Then
              Debug.Print singlecellw.Offset(0, 0).Address
              Debug.Print singlecellw.Offset(0, -22).Value
          End If
      Next singlecellw
  
  Set listofcellsx = Range("x4", Range("x3").End(xlDown))
      For Each singlecellx In listofcellsx
          If singlecellx.Value = "x1" Then
              Debug.Print singlecellx.Offset(0, 0).Address
              Debug.Print singlecellx.Offset(0, -23).Value
          End If
      Next singlecellx
  
  Set listofcellsy = Range("y4", Range("y3").End(xlDown))
      For Each singlecelly In listofcellsy
          If singlecelly.Value = "x1" Then
              Debug.Print singlecelly.Offset(0, 0).Address
              Debug.Print singlecelly.Offset(0, -24).Value
          End If
      Next singlecelly
  
  Set listofcellsz = Range("z4", Range("z3").End(xlDown))
      For Each singlecellz In listofcellsz
          If singlecellz.Value = "x1" Then
              Debug.Print singlecellz.Offset(0, 0).Address
              Debug.Print singlecellz.Offset(0, -25).Value
          End If
      Next singlecellz
  
  'Worksheets("allocation").Activate
  
  
  End Sub
  </code></pre>