ThisWorkbook

[klatlap]

I am having a few issues where i run similar macros in different workbooks and i am trying to separate them as they may be running at the same time.

I read about ThisWorkbook functions and was wondering if i have to use it at the beginning of each reference to a sheet or cell or can it be set at the beginning of the macro.

Sub Import()
    Application.ScreenUpdating = False
    Sheets("Track").Select
    Range("Z1:AL100").Select
    Selection.ClearContents
    If ThisWorkbook.Sheets("Control").Range("AM2").Value = "" Then
    GoTo Xit
    Else
    Range("Z2").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & Sheets("Control").Range("AM2").Value, Destination:=Range( _
        "$Z$2"))
        .Name = False
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Xit:
Application.ScreenUpdating = True
End If
Sheets("Control").Select
Clr:    For I = 1 To ActiveWorkbook.Connections.Count
If ActiveWorkbook.Connections.Count = 0 Then Exit Sub
ActiveWorkbook.Connections.Item(I).Delete
I = I - 1
Next I
End Sub

 

[venkat1926]

not clear

I am sure you know

thisworkbook means the workbook where the macro is parked
activeaworkbook is the workbook which is active


There are thisworkbook codes. To see them

open vb editor (alt+F11)
hit control+R. You see project code on the left
go to the file you are interested
rightclick "thisworkbook" and click "view code"
click left side arrow and choose "workbook:
on the right side you see the list of event code on which event occurring the code statement will run automatically. for example when this workbook opens a certain set of code statements can run.

 

[Rasm]

You can have one code that executes across many workbooks and many sheets. But generally you should not place code in ThisWorkBook - there is exceptions. Most code should go into the module section. The only real exception is your events - you can create your own events but they normally go into a class module.
So I am not entirely sure what your question is - but if multiple workbooks use the same sheet names - then you 4 sure need to reference the workbook. Now what I am saying here applies to ActiveX controls - I dont use Form controls very much - not sure what you are using. Dont know if this helps.

 

[klatlap]

Yes the sheet names are the same on many of my workbooks but they all work independently of each other, i use these sheets for trigger betting with Gruss.

 

[mikerickson]

When working with multiple workbooks, variables of type Workbook can be used. Here's an example

Sub test()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("Workbook2.xlsm")
    
    wb1.Sheets("Sheet1").Range("A1").Value = wb2.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Value
End Sub