Consulting

Results 1 to 5 of 5

Thread: Different results when execute code by "F5" or "F8" (step by step).

  1. #1
    VBAX Regular
    Joined
    May 2016
    Posts
    8
    Location

    Different results when execute code by "F5" or "F8" (step by step).

    Hi,
    Below code works fine when I use F5 down to Stop, and then use F8 for the last 4 lines.
    With fine I mean wb2 is shown on my screen.
    If I run full code with F5 (without Stop), wb1 is shown on my screen.
    Why close and open wb1? I want it open with conditional formating, as you can see I delete CF in the beginning of code.
    If I use "ThisWorkbook" instead of "ActiveWorkbook", "PERSONAL.XLSB" comes up after code is executed.
    I want to store code in "PERSONAL.XLSB", not in wb2.
    What am I doing wrong? I want wb2 to show up on my screen after code is executed.

    Sub CopyStuff()
    
    
    Dim wb1     As Workbook, wb2 As Workbook
    Dim ws1     As Worksheet, ws2 As Worksheet
    Dim rng     As range, rng2 As range, rng3   As range, rng4   As range
    Dim Arr     As Variant
    Dim cell    As range
    Dim Scell   As range
    Dim i
    Dim j
    Dim TP      As Variant
    Dim DT      As Variant
    Dim AO      As Variant
    
    
    'Set wb2 = ThisWorkbook
    Set wb2 = ActiveWorkbook
    Set ws2 = wb2.Sheets("Sheet1")
    Set wb1 = Workbooks.Open("C:\Users\Path\Test.xlsm")
    Set ws1 = wb1.Sheets("Rank")
    Application.ScreenUpdating = False
    '------------------------------------------------------
    'Sub Keep_Format()
    ws1.Activate
        Dim mySel As range, aCell As range
        Set mySel = ws1.range("B1:CK12")
        For Each aCell In mySel
            With aCell
              .Font.FontStyle = .DisplayFormat.Font.FontStyle
              .Font.ColorIndex = .DisplayFormat.Font.ColorIndex
              .Interior.Color = .DisplayFormat.Interior.Color
              .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
            End With
        Next aCell
        mySel.FormatConditions.Delete
    '-------------------------------------------------------
    wb2.Activate
    ws2.range("B1").Activate
    Set Scell = ws2.range("B1")
    Set rng = ws1.range("B1:CK12")
    Set rng3 = ws2.range("C23:C34")
    
    
    With rng
    'My Code
    End With
    'Stop
    wb1.Close SaveChanges:=False
    Set wb1 = Workbooks.Open("C:\Users\Path\Test.xlsm")
    wb2.Activate
    Application.ScreenUpdating = True
    End Sub
    I hope all this text makes sense. If not please ask for clarification.
    Any help will be greatly appreciated.
    Thanks!

  2. #2
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    4
    Location
    Did you try to remove
    Set wb1 = Workbooks.Open("C:\Users\Path\Test.xlsm")
    from the end of file?
    Can't understand why you close this file and open it again.

  3. #3
    VBAX Regular
    Joined
    May 2016
    Posts
    8
    Location
    Hi penera,
    I need to copy and paste values and only format, not conditional format (CF). To do that I use code above ("Keep_Format").
    To keep CF in that workbook, I need to close it without saving.
    When I open it again it still has CF.

  4. #4
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I think your problem is a simple timing problem, you kick off the opening of wb1 but you don't do anything with it, so excel carries on and activates wb2. if the wb1 opens slowly then it will grab focus when it finally opens.
    I think the way round this is to activate a sheet in wb1 before you activate wb2 , this will force excel to wait.
    e.g.:
    Set wb1 = Workbooks.Open("C:\Users\Path\Test.xlsm")
    ws1.Activate
    wb2.Activate

  5. #5
    VBAX Regular
    Joined
    May 2016
    Posts
    8
    Location
    Hi offthelip,
    I thought so too and did a lot of experimenting to no avail.
    I tried your suggestion but got "Automation error".
    Anyway I managed to solve the problem.
    Solution: Move "Application.ScreenUpdating = True" right before "wb1.Close SaveChanges:=False".
    Thanks for your time.

Tags for this Thread

Posting Permissions

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