title: Transferring data from one workbook to another workbook daily into specific/empty cells
body: <p>I am in the process of developing a macro that will Transfer Data from one workbook <code>(enterdata)</code> to another workbook <code>(extractdata)</code> while having a mass collection of formulas in the <code>extractdata</code> workbook. I need to have the data extracted into specific cells as data gets input day by day. This data needs to be compiled and I am having troubles with this portion. </p>
  
  <p>I need the data extracted into specific cells because if I use the empty row functions I have formulas at the end of my rows and bottom of my columns therefore I am trying to have the data input into specific cells</p>
  
  <p>I think I am on the right track but I continuously have <code>Run-time error '1004':</code> stating <code>select method of range class failed</code>.</p>
  
  <p>Thanks in advance,
  Anderson</p>
  
  <pre><code>'Next the transfers of the data from the daily quality activity to the specific worksheet of the unit charts'
  'Accesories'
  Set myData = Workbooks.Open("C:\database\Extractdata.xlsm")
  Worksheets("ACC").Select
  
  Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
      Dim currentRowValue As String
  
      sourceCol = 1   'column A has a value of 1
      rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
  
      'for every row, find the first blank cell and select it
      For currentRow = 3 To rowCount
          currentRowValue = Cells(currentRow, sourceCol).Value
          If IsEmpty(currentRowValue) Or currentRowValue = "" Then
              Cells(currentRow, sourceCol).Select
          End If
      Next
  
  With Worksheets("ACC").Range("A3")
  .Offset(ColumnCount, 0) = currentDate
  .Offset(ColumnCount, 1) = devicesStockedACC
  .Offset(ColumnCount, 2) = qipAttemptsACC
  .Offset(ColumnCount, 3) = qipncproductCountACC
  .Offset(ColumnCount, 4) = totalqcncCountACC
  .Offset(ColumnCount, 5) = devicencidCountACC
  .Offset(ColumnCount, 6) = componentncidCountACC
  End With
  
  
  
  NEW CODE 3/13/14
  
  
  Dim currentDate As String
  
  Dim devicesStockedACC As String
  Dim qipAttemptsACC As String
  Dim qipncproductCountACC As String
  Dim totalqcncCountACC As String
  Dim devicencidCountACC As String
  Dim componentncidCountACC As String
  
  'Acessories'
  Worksheets("tuesday").Select
  currentDateACC = Range("A1")
  Worksheets("tuesday").Select
  devicesStockedACC = Range("C12")
  Worksheets("tuesday").Select
  qipAttemptsACC = Range("D12")
  Worksheets("tuesday").Select
  qipncproductCountACC = Range("E12")
  Worksheets("tuesday").Select
  totalqcncCountACC = Range("H12")
  Worksheets("tuesday").Select
  devicencidCountACC = Range("L12")
  Worksheets("tuesday").Select
  componentncidCountACC = Range("M12")
  
  
  'Next the transfers of the data from the daily quality activity to the specific worksheet of the unit charts'
  'Accesories'
  Set myData = Workbooks.Open("C:\database\Extractdata.xlsm")
  Worksheets("ACC").Select
  Worksheets("ACC").Range("A3").Select
  
  Application.Run "Selectfirstblankcell"
  
  
  
  Sheets("ACC").Activate
  
  With Worksheets("ACC").Range(ActiveCell)
  .Offset(ColumnCount, 0) = currentDate
  .Offset(ColumnCount, 1) = devicesStockedACC
  .Offset(ColumnCount, 2) = qipAttemptsACC
  .Offset(ColumnCount, 3) = qipncproductCountACC
  .Offset(ColumnCount, 4) = totalqcncCountACC
  .Offset(ColumnCount, 5) = devicencidCountACC
  .Offset(ColumnCount, 6) = componentncidCountACC
  .Offset(ColumnCount, 8) = currentDate
  .Offset(ColumnCount, 15) = currentDate
  End With
  
  
  
  Application.Run "Selectfirstblankcell" = 
  
  Public Sub SelectFirstBlankCell()
      Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
      Dim currentRowValue As String
  
      sourceCol = 1   'column F has a value of 6
      rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
  
      'for every row, find the first blank cell and select it
      For currentRow = 3 To rowCount
      For currentCol = 1 To ColCount
          currentCellValue = Cells(currentRow, currentCol).FormulaR1C1
          If IsEmpty(currentCellValue) Or currentCellValue = "" Then
              Cells(currentRow, currentCol).Select 'do something else than select maybe?
              'put code here to handle the empty cell
              Exit For 'this will exit the inner loop, so it will only process the first blank cell on each row
          End If
      Next
  Next
  End Sub
  </code></pre>