Macro not continuing to run after simulating AltTab ("%{TAB}") to get back to original workbook

[Usually_Confused]

Hi,

First of all, thanks so much to everyone for all the solutions I've benefitted from here over the years. I've always been able to find what I need by searching and combining the various bits of code to get what I want. Hence this being my first cry for help - I just can't figure this out.

Basic premise of this workbook is for the sales team to be able to do their work on one workbook with cost prices, VLOOKUPS etc and export the work to a customer friendly sheet so that there is no risk of them sharing costs or annoying customers with formula-ridden proposals. All straightforward enough, done it plenty of times. I usually get back to the orignal workbook by calling on it by name. But this time I fancied giving them the ability to be not bound to maintaining the same filename but instead be able choose their filename for each customer.

It all works fine until I have used ("%{TAB}") to get back to the original workbook. All I want to do is hide the temporary working sheet 'Cust_Copy_Blank' once everything else is completed. I just can't get the code to continue to run. Strangely though, I can get a simulated mouse click to work. I only tried that, actually, in case the fact that the whole sheet's cells were still selected (after the copy to new workbook) was causing the workbook to be not activated properly or something. Seems that wasn't the problem.

Hope someone can help!

I've added comments at the key stages so that it easier to understand the flow of events. Hopefully that will be helpful.

Private Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cbuttons As Long, ByVal dwExtraInfo As Long)
  
Const MOUSEEVENTF_LEFTDOWN As Integer = 2
Const MOUSEEVENTF_LEFTUP As Integer = 4
Const MOUSEEVENTF_RIGHTDOWN As Integer = 8
Const MOUSEEVENTF_RIGHTUP As Integer = 16

Sub Cust_Copy_2()
'
' Cust_Copy_2 Macro
'

'unhiding and clearing the contents of the working sheet from any previous work

    Sheets("Cust_Copy_Blank").Visible = True
    Sheets("Cust_Copy_Blank").Select
    
        Cells.Select
    Selection.ClearContents
        Columns("A:N").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
        Cells.Select
    Selection.RowHeight = 14
    
' Copying and pasting as values and formats the completed work from the formula sheet to the working sheet
  
    Sheets("Customer Wine List").Select
    Cells.Select
    Selection.Copy
    Range("A1").Select
    
    Sheets("Cust_Copy_Blank").Select
    Range("A1").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

' deleting some sensitive pricing information and copying to a new book

    Range("E17").Select
    Columns("I:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("B2").Select
            
    ActiveSheet.Copy
    
 ' forcing to enter a customer name for the new worksheet
 
        Dim newName As String
 
    On Error Resume Next
    newName = InputBox("Enter the name for the copied worksheet")
 
    If newName <> "" Then
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = newName
    End If
     Sheets("Cust_Copy_Blank").Select
     
Application.DisplayAlerts = False

    ActiveSheet.Delete

Application.DisplayAlerts = True


'some final formatting on the new worksheet

Call print_setup
Call wrap_text
Call repeat_rows

'forcing to save the file with customer name

MsgBox "Now enter the name to save your customer copy"

Call SaveFile

'Simulating Alt Tab to get back to master workbook in order to hide Cust_Copy_Blank working sheet

Application.SendKeys ("%{TAB}")
Application.Wait Now + TimeValue("0:00:01")
Application.SendKeys "1", True
     


Call mouse_click

'nothing runs after this point but no errors either. All I want to do is hide that working sheet for neatness. Just the mouse click is simulated successfully and then nothing.
'I only added the mouse click in case the whole sheet being previously selected was causing the issue

Call hide_CustCopy ' in which I have tried all of the below in the main code and as a separate Sub

'ActiveWorkbook.Sheets("Cust_Copy_Blank").Visible = xlSheetHidden
'Worksheets("Cust_Copy_Blank").Visible = xlSheetHidden
'Sheets("Cust_Copy_Blank").Visible = False


Sheets("Customer Wine List").Select

       
End Sub

 

[BSALV]

while you are in your new customer file, you can hide the "cust_copy_blank" sheet in the original workbook by this line (thisworkbook refers to that file), you don't need sendkeys

  ThisWorkbook.Sheets("Cust_Copy_Blank").Visible = False
     'Application.SendKeys ("%{TAB}")
     'Application.Wait Now + TimeValue("0:00:01")
     'Application.SendKeys "1", True

 

[Usually_Confused]

while you are in your new customer file, you can hide the "cust_copy_blank" sheet in the original workbook by this line (thisworkbook refers to that file), you don't need sendkeys

  ThisWorkbook.Sheets("Cust_Copy_Blank").Visible = False
     'Application.SendKeys ("%{TAB}")
     'Application.Wait Now + TimeValue("0:00:01")
     'Application.SendKeys "1", True

Ah, amazing. Thank you! That works.

Initially couldn't at all fathom how that could have worked. Am I right in thinking though that, because the running macro exists in the original workbook, the term 'ThisWorkbook' therefore simply instructs it to refer to the workbook where the running macro exists rather than the newly created workbook where it was last doing work?

 

[BSALV]

the one, you're working in, is the activeworkbook

 

[Usually_Confused]

the one, you're working in, is the activeworkbook

Got it, thanks.

As an alternative method, I managed to get it to work with ActiveWindow.ActivateNext in case that is ever of any use to anyone. The code continued to run after using that to switch backwards and forwards.

ActiveWindow.ActivateNext
'Application.SendKeys ("%{TAB}") ', True
'Application.Wait Now + TimeValue("0:00:01")
'Application.SendKeys "1", True

I preferred BSALV's method though

Thanks again BSALV and to everyone generally!