Application Workbooks V WBNameOutPut Activate alternative

Question

I use this piece of code:

Application.Workbooks(V_WBNameOutPut).Activate

I have the following code for calling a function and duplicating the above line's borders and orientation. How can I change the code so that I may enter alpha numeric values in that cell as this only accepts numeric values?

Answer 1

You can capture the workbook when it's opened and you don't have to worry about the extension after that.

Sub Test()

    'Open the first workbook and store reference to it.
    Dim wrkBk1 As Workbook
    Set wrkBk1 = Workbooks.Open("H:\Darren Bartrup-Cook\Test 1.xlsx")
    
    'Open the second workbook and store reference to it.
    Dim wrkBk2 As Workbook
    Set wrkBk2 = Workbooks.Open("H:\Darren Bartrup-Cook\Test 2.xlsx")
    
    'Copy/paste from wrkbk1 to wrkbk2.
    wrkBk1.Worksheets("Sheet1").Range("A1").Copy Destination:=wrkBk2.Worksheets("Sheet1").Range("A4")
    
    'Create a new sheet in wrkbk2.
    Dim NewWrkSht As Worksheet
    Set NewWrkSht = wrkBk2.Worksheets.Add
    NewWrkSht.Name = "My New Sheet"
    
    'Paste copy/paste values from wrkbk1 to wrkbk2.
    wrkBk1.Worksheets("Sheet1").Range("A2").Copy
    NewWrkSht.Range("A5").PasteSpecial Paste:=xlPasteValues
    
    'Make A3 in wrkbk2 equal the value in wrkbk1 A3.
    wrkBk2.Worksheets("Sheet1").Range("A3") = wrkBk1.Worksheets("Sheet1").Range("A3")
    
    'Close the two workbooks.
    wrkBk2.Close SaveChanges:=True
    wrkBk1.Close SaveChanges:=False
    
End Sub