PDA

View Full Version : [SOLVED] Different results when execute code by "F5" or "F8" (step by step).



bananas
06-18-2018, 04:41 AM
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!

penera
06-18-2018, 06:39 AM
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.

bananas
06-18-2018, 09:29 AM
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.

offthelip
06-18-2018, 04:02 PM
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

bananas
06-19-2018, 04:07 AM
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.