title: vba excel counta
body: <pre><code>Cells(4, x) = Application.WorksheetFunction.COUNTA(Workbooks(""DB_Report.xls"").Sheets(x).Range(A:A))
  </code></pre>
  
  <p>I am trying to get the above function to work. </p>
  
  <p>I am calling the script from the workbook <code>DB_report.xls</code> </p>
  
  <p>This creates a new workbook ("month") and starts filling in the values.</p>
  
  <p>What I am trying to get to is where </p>
  
  <p>cell <code>4,1</code> in months has the counta of sheet 1 from DB_report</p>
  
  <p>cell <code>4,2</code> in months has the counta of sheet 2 from DB_report</p>
  
  <p>Can anyone reword the line above so when "months is the active work sheet I can call counta from DB_Report</p>
  
  <p>The line before this is </p>
  
  <pre><code>NameSH = Workbooks("DB_Report.xls").Sheets(x).Name and this works fine and returns the name of work sheet x
  </code></pre>
  
  <p>Thanks </p>
  
  <p>Aaron</p>
  
  <p>Ok for a bit further explicanation</p>
  
  <p>the steps I want to do go some thing like this </p>
  
  <p>select workbook months.xls
  select sheet(1)
  cell (x,y)  = counta( of range A:A , in worksheet("DB_Report") of worksheet (DB_report.xls) </p>
  
  <p>Now I know </p>
  
  <p>Cells(4, x) = Application.WorksheetFunction.COUNTA(sheet(3).range(a:A)</p>
  
  <p>will work with in the active work sheet. So if the active sheet is sheet 1 then that would count up he numbe of cells in sheet 3 of the same workbook. I wanted to know if as well as refrenced sheet and cells in the function I can also refrence a workbook by name. </p>
  
  <p>of course i could swqap to book "DB_Report" save the value to a varible and then swap back to book "Month" and copy it to the cell. </p>
  
  <p>or could I do workbook("month").sheet(y).cells(a,b) = Application.WorksheetFunction.COUNTA(sheet(3).range(a:A)</p>
  
  <p>while in workbook "month"</p>
  
  <p>so really what i need is how do you refrence workbook,sheet and cells all with in a function?</p>