Results 1 to 3 of 3

Thread: Trouble switching between workbooks after Workbooks.Open(sPath)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Trouble switching between workbooks after Workbooks.Open(sPath)

    I have the following code which appears to get stuck after I open a new workbook (EMEA). I would like to be able to filter the first workbook which is already open (data), and copy and paste between the two. As soon as I open the new workbook, I cannot navigate back to the data workbook and the code doesn't run as it should. When I step through the code to debug it, it works normally which is weird (although I have to manually click back to the data workbook first). I'm calling Sub copy() from another sub command if that makes a difference and the macro is saved in PERSONAL.XLSB

    [code]
    Public wb As Workbook
    Public sPath As String
    Public LR As Long

    Sub copy()

    today = Format(Date, "mm-dd-yyyy")

    sPath = "S:\XXXXX\EMEA.xlsx"
    Set wb = Workbooks.Open(sPath)

    Windows("Data (" & today & ").xlsx").Activate
    ActiveSheet.ShowAllData

    ActiveSheet.Range("$A$1:$CU$20000").AutoFilter Field:=4, Criteria1:="EMEA"

    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("D2:D" & LR).Select
    Selection.Copy

    Windows("EMEA.xlsx").Activate
    Range("A3").Select
    ActiveSheet.Paste
    [\code]


    Thanks in advance!
    Last edited by onetap; Aug 14th, 2019 at 05:20 PM.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,143
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Trouble switching between workbooks after Workbooks.Open(sPath)

    Does this work?
    Code:
    Sub copy()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim sPath As String
    Dim LR As Long
    Dim today As String
    
        Set wbSrc = ActiveWorkbook
        
        today = Format(Date, "mm-dd-yyyy")
    
        sPath = "S:\XXXXX\EMEA.xlsx"
        
        Set wbDst = Workbooks.Open(sPath)
        
        With wbSrc.ActiveSheet
            .ShowAllData
            .Range("$A$1:$CU$20000").AutoFilter Field:=4, Criteria1:="EMEA"
            LR = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("D2:D" & LR).copy wbDst.ActiveSheet.Range("A3")
        End With
        
    End Sub
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Sep 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trouble switching between workbooks after Workbooks.Open(sPath)

    This did the trick! Thank you very much

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •