Copy Cell from an already open workbook to another using VBA

Question

This is my code:

Sub FillInternetForm()
    Dim fromPath As String
    Dim toPath As String

    fromPath = InputBox(prompt:="Enter FROM File FULL Path:", _
    Title:="FROM FILE", Default:="")

    toPath = InputBox(prompt:="Enter TO File FULL Path:", _
    Title:="TO FILE", Default:="")

    Workbooks(fromPath).Sheets("Sheet1").Range("F10").Copy
    Workbooks(toPath).Sheets("Sheet1").Range("F10").PasteSpecial

End Sub

I have 3 Workbooks (Macros.xlsm, CopyFrom.xlsx and CopyTo.xlsx). The above code is stored in Module 1 on the Macros.xlsm workbook.

Workbooks (CopyFrom.xlsx and CopyTo.xlsx) are already open...

What I need to do is: Copy value in cell (F10) from CopyFrom.xlsx workbook to cell (F10) in CopyTo.xlsx workbook...

I keep getting the following Error: "Subscript out of range"

Any help is appreciated!

If the books are open, you don't need full path. Just book names. — findwindow
I assume you have "Sheet1". Hmm maybe try worksheets instead of sheets but uh why do you need to prompt for names if you already know which books they are? Edit: if you don't need formatting just set the ranges to each other instead of copy paste. — findwindow

MatthewD MatthewD · Accepted Answer · 2015-10-05T19:57:07

You need to create objects for those workbooks.

Sub FillInternetForm()
    Dim fromPath As String
    Dim toPath As String
    Dim wbFrom As Workbook
    Dim wbTo As Workbook
    Dim fromValue As String

    fromPath = InputBox(prompt:="Enter FROM File FULL Path:", _
    Title:="FROM FILE", Default:="")

    toPath = InputBox(prompt:="Enter TO File FULL Path:", _
    Title:="TO FILE", Default:="")

    Set wbFrom = Workbooks.Open(Filename:=fromPath, ReadOnly:=True)
    Set wbTo = Workbooks.Open(Filename:=toPath, ReadOnly:=False)

    wbFrom.Activate
    fromValue = ActiveWorkbook.Sheets(1).Range("F10").Value

    wbTo.Activate
    ActiveWorkbook.Sheets(1).Range("F10").Value = fromValue

End Sub

I am not sure though why you say that they are already open and then you are asking for the full path to them. Maybe you can explain.

Copy Cell from an already open workbook to another using VBA

1 Answers