Trying to make it happen in order

S

superman0623

New member
Joined
Sep 24, 2019
Messages
2
Reaction score
0
Points
0
Location
Atlanta
Website
www.linkedin.com
Excel Version(s)
Office 365
Hello Everyone! I have a code that I am trying to create for a sheet, but when I run it everything occurs out of order. The main part I need for it to create the copy of the sheet before anything else occurs :eek2::eek2:

Code: 
Sub Test()
'
' Test Macro
'
'This creates a copy of the Data Sheet (2) the moves it to the end and selects it
Application.EnableEvents = False


    Sheets("Data").Select
    Sheets("Data").Copy After:=Sheets(2)
    Sheets("Data (2)").Select
    
'This Select the TTL B1 Column and then sets a conditional format around the amount to create yellow into cells that are above that amount
    Columns("W:W").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=4"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = True
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
 'This select all and autofits all columns and rows to properly fit all information
    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
'this centers all of the rows and columns vertically in the box
    With Selection
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'This centers all of the rows and columns vertically in the box
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'This creates all borders
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


'This Hides all colums that are not needed


    Range("A:A,B:B,I:I,M:M,N:N").Select
    Range("N1").Activate


    Range("A:A,B:B,I:I,M:M,N:N,P:P,Q:Q,R:R,S:S,U:U").Select
    Range("U1").Activate
    Selection.EntireColumn.Hidden = True
    
    
'This Selects what warehouse to choose
    ActiveSheet.Range("$A$1:$EF$32716").AutoFilter Field:=3, Criteria1:="718"
'This Select the Product Class
    ActiveSheet.Range("$A$1:$EF$32716").AutoFilter Field:=10, Criteria1:="46"
'This Selects the TTL B1 WOS and Sorts by largest to Smallest
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
        "W1:W32716"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    
 'This selects the Copied Data Sheet and Deletes it
    'Sheets("Data (2)").Select
    'ActiveWindow.SelectedSheets.Delete
Application.EnableEvents = True


End Sub
 
Last edited by a moderator:
Hi
in the future please wrap your code with code tags ( the #button). Thanks
 
Did you ever get this sorted out ?

If not try running the macro using the F8 key to step through one line at a time, perhaps you'll be able to see where things go astray.

One way of doing this would be to split your screen, half showing the sheets and the other showing the VBA.
To do this, bring the sheets up on the screen and hit the WindowsKey + left arrow, this will put the sheets on the left side of the screen. Bring up the VBA on the screen and hit WindowsKey + right arrow, this will put the VBA on the right half of the screen. Put the cursor anywhere in the macro and using F8 you can step through the code and see in the sheets half of the screen what's happening.

Hope this helps.
 
You must log in or register to reply here.
Back
Top