Copying cell data from Workbook A to Workbook B

  Hello,


I found a similar problem on here and used the code but cannot seem to get it right. I am attempting to copy data in a specific cell from the workbook, "set", to the workbook "read" in a specific cell and then increment.  Right now i can copy the value from F7 to Column C and increment. When i attempt to compile the code i recieve a run time error, 9, Subscript out of range for the last line, commented out, of code where i attempt to go from workbook to workbook.

 

I should also add that i want the workbook "read" to be closed and in a different file directory.

Sub Test_Serena()
  Dim mycell As Range  
 
  Set mycell = Cells(65000, 3).End(xlUp).Offset(1, 0)
  mycell.Value = Now
  mycell.Offset(0, 1).Value = Range("F7").Value
  Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1"))
 
  'Call CopyValues(ThisWorkbook.Worksheets("Sheet1").Range("A1:A5"), _
     Workbooks("read.xlsm").Worksheets("sheet1").Range("A1"))
End Sub
 
 
Sub CopyValues(rngSource As Range, rngTarget As Range)
    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
End Sub
Vishesh's picture

Copy - Reply

Ensure the the target workbook ("read.xlsm" in your case) is open while the code is run. It won't work on closed workbook.

 Is it possible to accomplish

 Is it possible to accomplish this on a closed workbook

Vishesh's picture

Yes it is

You can open the file (of course through VBA code) before copy values and the after copying values you can save and close the file.

Nick's picture

copy data from closed workbook

here's some code to open and close the workbook:
set myWb = workbooks.open("C:\TEMP\MyFile.xls)
 
' do stuff
 
myWb.Close(False)
replace: C:\TEMP\MyFile.xls with your file full path..