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
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