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!
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!