title: sub or function not defined copy pasting from one file to another
body: <p>I have the following code to copy yearly precipitation data from 132 different excel files to one large dataset.  I have precipitation data from multiple different sites which I am placing in different columns hence the different col values.  I also want to match the dates hence the rw value.  However, I am getting that my sub is not defined and I am not sure why.</p>
  
  <pre><code>Sub f()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
  
    Dim directory As String, fileName As String
    directory = "C:\Working-Directory\Precipdata"
    fileName = Dir(directory &amp; "*.csv")
    Do While fileName &lt;&gt; ""
      Workbooks.Open (directory &amp; fileName)
      With (Workbooks(directory &amp; fileName))
        If Range("B2").Value = "GJOA HAVEN A" Then col = "B &amp; rw :D &amp; rw+lngth-27"
        If Range("B2").Value = "TALOYOAK A" Then col = "E &amp; rw :G &amp; rw+lngth-27"
        If Range("B2").Value = "GJOA HAVEN CLIMATE" Then col = "H &amp; rw :J &amp; rw+lngth-27"
        If Range("B2").Value = "HAT ISLAND" Then col = " &amp; rw :M &amp; rw+lngth-27"
        If Range("B2").Value = "BACK RIVER (AUT)" Then col = "N &amp; rw :P &amp; rw+lngth-27"
        yr = Range("B27").Value
        lngth = (Range("B27").End(xlDown).Row)
      End With
      Workbook(Macroforprecip.xlsm).Activate
      rw = Cells.Find("01/01/" &amp; yr).Row
      Workbooks(fileName).Range("P&amp;R&amp;T" &amp; (Range("B27").End(xlDown).Row)).Copy_Workbooks(Macroforprecip.xlsm).Range (col)
      Workbooks(fileName).Close
      fileName = Dir()
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
  
  End Sub
  </code></pre>
  
  <p>Also I really wanted to use the cells function so I could make col a numeric value and just add two but I can't find how to do the equivalent of Range(""A"&amp; i : "G" &amp; i") with it.</p>
  
  <p>OK so I updated it to be slightly more simple.  I am only copying one column at a time and I did change the workbook() function to workbooks()  my new code looks like this.</p>
  
  <pre><code>Sub precipitation()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Dim directory As String, fileName As String
  directory = "C:\Working-Directory\Precipdata\"
  fileName = Dir(directory &amp; "*.csv")
      Do While fileName &lt;&gt; ""
          sheetName = Left(fileName, Len(fileName) - 4)
          Workbooks.Open (directory &amp; fileName)
          Workbooks(fileName).Activate
                  If Range("B1").Value = "GJOA HAVEN A" Then
                  col = "B"
                  End If
                  If Range("B1").Value = "TALOYOAK A" Then
                  col = "E"
                  End If
                  If Range("B1").Value = "GJOA HAVEN CLIMATE" Then
                  col = "H"
                  End If
                  If Range("B1").Value = "HAT ISLAND" Then
                  col = "K"
                  End If
                  If Range("B1").Value = "BACK RIVER (AUT)" Then
                      col = "N"
                  End If
              yr = Range("B27").Value
              lngth = (Range("B27").End(xlDown).Row)
         Workbooks("Macroforprecip.xlsm").Activate
             Set rw = ActiveSheet.Cells.Find(what:=DateValue("01/01/" &amp; yr))
             r = rw.Row
  
         Workbooks(fileName).Range("P27", "P" &amp; lngth).Copy_Workbooks("Macroforprecip.xlsm").Range (col &amp; r)
         Workbooks(fileName).Close
         fileName = Dir()
      Loop
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  
  End Sub
  </code></pre>
  
  <p>My new error is that I get "Run-time error '438':<br>
  Object doesn't support this property or method."</p>
  
  <p>This happens at the Line that actually does the copying over `</p>
  
  <pre><code>(Workbooks(fileName).Range("P27", "P" &amp; lngth).Copy_Workbooks("Macroforprecip.xlsm").Range (col &amp; r)`
  </code></pre>
  
  <p>I do not fully understand what this means and even more so I do not understand what to do about it.  Thank you all for the help so far.</p>