+ Reply to Thread
Results 1 to 2 of 2

Code to insert rows in next sheet in order of Column B

Hybrid View

kosherboy Code to insert rows in next... 04-24-2014, 09:48 AM
kosherboy Re: Code to insert rows in... 04-24-2014, 10:24 AM
  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Code to insert rows in next sheet in order of Column B

    Hi all.
    The below code used to work for me perfectly but in the last couple days it's been acting up. The code is supposed to cut out 2 rows from sheet1 and insert it in order of column B in sheet2. Up until a few days ago the code was inserting the 2, cut, rows properly but now it inserts it at the top of the sheet.

    Here is the workbook i'm working with TEST.xlsm

    Any help would be greatly appreciated

    Thanks.

    Sub INSERT_ROWS()
    Dim LR As Long
    
    With ActiveCell
      If .Column = 1 Then
        If (.Row Mod 2 = 0) Then Exit Sub
      Else
        Exit Sub
      End If
    End With
    
            
            If Selection.Column = 1 Then Selection.Resize(2, 1).EntireRow.Select
            Selection.Cut
            Sheets("Sheet2").Select
        Rows("5:5").Select
        Selection.Insert Shift:=xlDown
           
            LR = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("BA5").Select
        ActiveCell.Formula = "=B5"
    Range("BA6").Select
     ActiveCell.Formula = "=BA5"
        Range("BA5:BA6").Select
    
    If Application.IsOdd(LR) Then
        Range("BA5:BA6").AutoFill Destination:=Range("BA5:BA" & LR + 1)
    Else
        Range("BA5:BA6").AutoFill Destination:=Range("BA5:BA" & LR)
    End If
    
    Range("BA5:BA5000").Select
    Rows("5:5000").Select
        ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
            "BA5:BA5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet2").Sort
            .SetRange Range("A5:BA5000")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

  2. #2
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Code to insert rows in next sheet in order of Column B

    I just realized what the issue is!
    When I click on the last row to be inserted in the next sheet the row doesn't get sorted via the code but rather gets inserted to the top of the sheet.
    I'm still looking for a solution.

    By the way the code only works when I click on an odd cell that is in column A (see code)

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to cut two rows and insert them in different sheet in order of date
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2014, 12:58 PM
  2. Replies: 1
    Last Post: 10-22-2012, 03:26 AM
  3. Insert Column in alphabeticall order of name on next sheet
    By ufopilot3 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-13-2012, 08:45 AM
  4. [SOLVED] Code to cut and paste rows (insert cut cells) in a protected Excel sheet
    By bob.mc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-27-2012, 04:47 PM
  5. Replies: 1
    Last Post: 02-09-2012, 09:19 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1