VBA Copy and Paste Code with filename

[jpsimmon]

Hey All!
I have my trusty old glorified copy and paste code but I am looking to link the line that has my Workbook name to a cell in one of my sheets. Can't seem to figure it out.

Sub GrabIndEquity()

'Update Data

Workbooks.Open Filename:= _
        "T:\FILE1\FILE2\FILE3\FILE4\FILE5\DATABASE FILE.xlsm"
    Cells.Select
    Selection.Copy
     Windows("SUBJECT PROPERTY.xlsm").Activate
     ' I want to link the above Workbook name to a cell, IE, "SUBJECT PROPERTY"=to a cell in my workbook. Say Cell $A$1.
ActiveWorkbook.Worksheets("EQ").Activate
       Range("A1").Select
       ActiveSheet.Paste
       Application.CutCopyMode = False
     Windows("DATABASE FILE.xlsm").Activate
      ActiveWorkbook.Close
       'ActiveWorkbook.Save
    
    MsgBox ("EQ Data Has Been Updated")

        
End Sub

Anyone know how to do this?
And as always, if you spot any inefficiencies/upgrades to my code please add suggestions!

Thank you!

 

[gallen]

A little confused. Tidied up the code. Avoid using "Selection" and Activesheet / workbook etc. Easily done by assigning workbooks and worksheets to variable. Makes code easier to read and write.

Test this on a copy. It's untested. See if the behaviour is correct. Easily amended if not

Sub GrabIndEquity()

Dim wbDatabase As Workbook, wbSubjectProperty As Workbook


'Update Data

    Set wbDatabase = Workbooks.Open(Filename:= _
    "T:\FILE1\FILE2\FILE3\FILE4\FILE5\DATABASE FILE.xlsm")
    
    Set wbSubjectProperty = Workbooks("SUBJECT PROPERTY.xlsm")
    
    'This line copies and pastes the info all at once.
    wbDatabase.ActiveSheet.Cells.Copy wbSubjectProperty.Worksheets("EQ").Range("A1")
    Application.CutCopyMode = False
    
    ' I want to link the above Workbook name to a cell, IE, "SUBJECT PROPERTY"=to a cell in my workbook. Say Cell $A$1.
    'I was a little confused as to what you needed here. I guessed you wanted a link to the subject propoerty workbook in the database workbook
    
    wbDatabase.ActiveSheet.Range("A1").Hyperlinks.Add _
    Anchor:=Range("A1"), Address:=wbSubjectProperty.FullName, TextToDisplay:=wbSubjectProperty.Name
    
    wbSubjectProperty.Close
    wbDatabase.Activate
    'ActiveWorkbook.Save
    
    MsgBox ("EQ Data Has Been Updated")

        
End Sub

 

[jpsimmon]

Thanks Gallen,
Yeah I explained it a little poorly. What I want is to duplicate this workbook without having to go into the VBA every time and renaming the "SUBJECT PROPERTY.xlsm" to match the new workbook. I just want to link the workbook name in my code to a cell "A1" in worksheet "INFO".

Sub GrabIndEquity()

Dim wbDatabase As Workbook, wbSubjectProperty As Workbook
'Update Data

    Set wbDatabase = Workbooks.Open(Filename:= _
    "T:\FILE1\FILE2\FILE3\FILE4\FILE5\DATABASE FILE.xlsm")
    
    Set wbSubjectProperty = Workbooks("SUBJECT PROPERTY.xlsm")
    'So right here I want Workbooks("SUBJECT PROPERTY = Cell "A1" in worksheet "Info".xlsm")
    'This line copies and pastes the info all at once.
    wbDatabase.ActiveSheet.Cells.Copy wbSubjectProperty.Worksheets("EQ").Range("A1")
    Application.CutCopyMode = False
    
    ' I want to link the above Workbook name to a cell, IE, "SUBJECT PROPERTY"=to a cell in my workbook. Say Cell $A$1.
    'I was a little confused as to what you needed here. I guessed you wanted a link to the subject propoerty workbook in the database workbook
    
    wbDatabase.ActiveSheet.Range("A1").Hyperlinks.Add _
    Anchor:=Range("A1"), Address:=wbSubjectProperty.FullName, TextToDisplay:=wbSubjectProperty.Name
    
    wbSubjectProperty.Close
    wbDatabase.Activate
    'ActiveWorkbook.Save
    
    MsgBox ("EQ Data Has Been Updated")

        
End Sub