Results 1 to 4 of 4

Thread: Trying to make it happen in order

  1. #1
    Neophyte superman0623's Avatar
    Join Date
    Sep 2019
    Location
    Atlanta
    Posts
    2
    Articles
    0
    Excel Version
    Office 365

    Trying to make it happen in order



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    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 Pecoflyer; 2019-09-25 at 09:12 AM.

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,675
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    in the future please wrap your code with code tags ( the #button). Thanks
    Thank you Ken for this secure forum.

  3. #3
    Neophyte superman0623's Avatar
    Join Date
    Sep 2019
    Location
    Atlanta
    Posts
    2
    Articles
    0
    Excel Version
    Office 365
    Will do thanks!

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

Posting Permissions

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