Call "ThisWorkbook"

Question

I am trying to switch between a template (hard coded) and a dynamic report which changes name weekly (ThisWorkbook). I am struggling with calling the variable x to bring focus to the workbook. I am copying the template formulas and pasting them into the dynamic report.

Sub wkbk()
   Dim x As Excel.Workbook
   Set x = ThisWorkbook
   Dim pth As String
   pth = x.FullName
   Windows(pth).Activate
End Sub

Here is the VBA code I am using:

Windows("BBU_CMD_TEMPLATE.xlsx").Activate
Cells.Select
Selection.Copy
Windows(pth).Activate
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste

Wouldn't it just be `x.Activate`? Also, why are you needing to activate a workbook if you want to copy data to/from it? — BruceWayne, May 09 '17 at 16:34
ThisWorkbook is the workbook running the code. If you are trying to refer to an outside workbook you will need another way of referring to it. — Brandon Barney, May 09 '17 at 16:36

score 3 · Accepted Answer · edited May 23 '17 at 11:54

Why not just use ThisWorkbook.Activate? There's generally no need to assign a variable to represent a built-in like ThisWorkbook so the rest of those variables are unnecessary unless you're using them elsewhere in that procedure (from the snippet provided, you aren't, so you don't need them).

Sub wkbk()
   ThisWorkbook.Activate
End Sub

However, what's the point of wkbk procedure? If solely to activate the workbook, that's not needed either and there are plenty of reasons to avoid Activate.

Sub CopySheetFromTemplateToThisWorkbook()
Dim tmplt As Workbook
On Error Resume Next
Set tmplt = Workbooks("BBU_CMD_TEMPLATE.xlsx")
If tmplt Is Nothing Then
    MsgBox "Template file needs to be open..."
    Exit Sub
End If
On Error GoTo 0
With ThisWorkbook    
    tmplt.ActiveSheet.Copy After:=.Sheets(.Sheets.Count)
End With
End Sub

Call "ThisWorkbook"

1 Answers1