Copy paste from already opened excel to already opened excel

[MPFraser7]

I want to copy a range of cells from an already opened excel workbook (SourceWb) to another already opened excel workbook (TargetWb). I have the following macro but it is trying to open the SourceWb which is already opened. I need to modify it so that it recognizes that the SourceWb is already opened. I'm not sure how to do so, please help. The FilePath is stored in the worksheet "path" so it's easier to change for users.

Private Sub CommandButton1_Click()

Dim filePath As String
Dim SourceWb As Workbook
Dim TargetWb As Workbook

Set TargetWb = ActiveWorkbook

filePath = TargetWb.Sheets("Path").Range("A1").Value
Set SourceWb = Workbooks.Open(filePath)

SourceWb.Sheets("In Year Transfers").Range("S13:S195").Copy Destination:=TargetWb.Sheets("InYearTransfers").Range("A2:A196")

MsgBox "Refresh Complete"

End Sub

 

[Joe4]

What is the name of the Source workbook?
You can reference it something like this:

Windows("FileName.xlsx").Activate
Set SourceWb = ActiveWorkbook

 

[MPFraser7]

The filename is stored in: TargetWb.Sheets("Path").Range("A1").Value

I don't want to reference the filename directly in the macro since it might change.

I like your idea, but is there a way to do it by referencing to cell A1 from the Path sheet instead of writing the filename in the vba code?

Thanks

 

[Joe4]

What exactly does the entry in A1 look like?
Does it have the file path or file extension?
If not, what will be the file extension?
You basically just want the file name (no path) and the extension).

You could do something like:

Dim myFileName as String
myFileName = [COLOR=#333333]TargetWb.Sheets("Path").Range("A1")
Windows(myFileName).Activate
[/COLOR][COLOR=#333333]Set SourceWb = ActiveWorkbook[/COLOR]

depending on what is in cell A1.