keep thisworkbook as the workbook that is displayed after macro is finished running

[user125]

Hi all,

I have created a code that copies data from one workbook (Workbook1) and pastes it into another workbook (Worbook2). The code works fine but after it is finished, it displays Workbook2 on the computer screen. I would like Workbook1 to be the workbook that shows up after the code is done running. The code is in Workbook1 as well. I have tried various lines of code but I have not achienved what I am looking for. Any help would be appreciated! Thanks in advance! and hope everyone is staying safe!

 

[GWteB]

Post your code so we can see where it's going wrong.

 

[user125]

I am not sure if this will make any sense as there is a lot going on but in simple words this is a copy paste from one workbook into another. The line of code I have added for "Workbook1" to be the workbook to display after the macro has finished running is "Thisworkbook.Activate" but that didn't seem to work.

Public Sub TiBulkAdd(ws As Worksheet)

    Dim i As Long, x As Long, y As Long, z As Long
    Dim lastRow As Long
    Dim lrow As Long
    Dim lastr As Long
    Dim first As Long, last As Long
    Dim pathname As String
    Dim foldername As String
    Dim wb As Workbook
    Dim count As Long
    
    'checks if file is open, if not open, triggers the file to open
    Application.ScreenUpdating = False
    pathname = Mapping.Range("Task_List_Path")
    foldername = Mapping.Range("Task_List")
    If InStr(foldername, "TaskList") > 0 Then
    If CheckFileIsOpen(foldername) = False Then
    Workbooks.Open pathname & foldername
    Application.Run "'" & foldername & "'!InitiateBulkAdd"
    ElseIf CheckFileIsOpen(foldername) = True Then
    End If
    End If
    
    Set wb = Workbooks(foldername)
    lastRow = wb.Worksheets("Bulk_Add").Cells(Rows.count, "C").End(xlUp).Offset(1, 0).row
    lrow = wb.Worksheets("Bulk_Add").Cells(Rows.count, "M").End(xlUp).Offset(1, 0).row
    lastr = wb.Worksheets("Bulk_Add").Cells(Rows.count, "D").End(xlUp).Offset(1, 0).row
    first = ws.Range("Trigger_Table").row
    last = ws.Cells(Rows.count, "C").End(xlUp).Offset(-1, 0).row
    lastRow = lrow
    
    'adds non-duplicate task name, task description, umbrella to bulk add
    For count = first To last
        If ws.Range("C" & count).value = "ti" Then
            ws.Range("D" & count).Copy
                For i = lastRow To lastRow
                    wb.Worksheets("Bulk_Add").Range("C" & i + x).PasteSpecial Paste:=xlPasteValues
                Next i
                x = x + 1
            ws.Range("Umbrella").Offset(0, 1).Copy
                For i = lastr To lastr
                    wb.Worksheets("Bulk_Add").Range("D" & i + z).PasteSpecial Paste:=xlPasteValues
                Next i
                z = z + 1
            If ws.Range("E" & count).value = "" Then
            ws.Range("Description").Offset(0, 1).Copy
            Else
            ws.Range("E" & count).Copy
            End If
                For i = lrow To lrow
                    wb.Worksheets("Bulk_Add").Range("M" & i + y).PasteSpecial Paste:=xlPasteValues
                Next i
                y = y + 1
            ws.Range("C" & count).value = "ti- added to bulk upload"
        End If
    Next count
    
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    ThisWorkbook.Activate
End Sub

 

[GWteB]

I can not put a finger on it. It should work but I understand it does not. Which workbook remains the active one? Is it the one opened with this line of code
Workbooks.Open pathname & foldername (later on "wb")

 

[user125]

Yes, that is the workbook that stays open after the code has finished running.

 

[GWteB]

Did you try to move the line ThisWorkbook.Activate upwards, right after the two end ifs so after the invoked macro has finished?
Did you step through the code with F8 key?

 

[MARK858]

@user125, out of interest where does the code below take you if you place it where the

ThisWorkbook,Activare

is?

Application.Goto ThisWorkbook.Sheets(1).Cells(1, 1), True

 

[user125]

Did you try to move the line ThisWorkbook.Activate upwards, right after the two end ifs so after the invoked macro has finished?
Did you step through the code with F8 key?

Moving the code worked perfectly. That was such an easy fix! Thank you!

 

[user125]

@user125, out of interest where does the code below take you if you place it where the

ThisWorkbook.Activate

is?

Application.Goto ThisWorkbook.Sheets(1).Cells(1, 1), True

This worked perfectly, @MARK858. However, it takes me to the first sheet of the workbook and not the active sheet. Would you happen to know how I can change it to open to the active sheet. I can't name the sheet as it keeps changing and so does the position of the sheet. Thanks!

 

[MARK858]

You set the ActiveSheet as a variable at the start of the code and then use the Goto at the end to point to the variable.