Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Sort cells between 2 sheets

    Hi,

    I have a quite challenging goal and I hope you guys can help me. I have an excel file with 2 sheets. "Sheet 1" is "Current stock", "Sheet 2" is "Past Sales".
    "Sheet 1" contains "item name", "quantity" and some other data. I would like to automate the process of moving the entire row to "Sheet 2" once the quantity of items is set to Zero.
    Is this possible?

    I would be happy even with using a formula to copy information to "Sheet 2" once the quantity is Zero and use a filter that would automatically hide the line on "Sheet 1" based on quantity or format. (Sheet 1 has a conditional formatting that turns the item names bold once the quantity is set to Zero)

    Thanks for taking the time to read this...

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Welcome to the Lounge as a new poster!

    Paste this code into the Current Stock Sheet module:
    Code:
    Sub Worksheet_Change(ByVal Target As Excel.Range)
    
       Dim rngCell    As Range
       Dim shtPS      As Worksheet
       Dim lTargetRow As Long
       
       Set rngCell = Intersect(Target, Range("B:B"))
       
       If Not rngCell Is Nothing Then
        
         Application.ScreenUpdating = False
         Set shtPS = Worksheets("PastSales")
         
         If rngCell.Count = 1 Then   'Prevents multiple selection
           Application.EnableEvents = False
           If rngCell.Value = 0 Then
           
             lTargetRow = shtPS.Cells(Rows.Count, 1).End(xlUp).Row() + 1
             
             rngCell.EntireRow.Copy Destination:=shtPS.Cells(lTargetRow, 1)
             rngCell.EntireRow.Delete
             
           End If   'rngcell.value = 0
           
         End If  'rngCell.Count
         
       End If
    
       Set rngCell = Nothing
       Application.EnableEvents = True
       Application.ScreenUpdating = False
      
    End Sub    'Worksheet_Change()
    Make sure to save the file as type .xlsm or .xlsb!

    Note: that I took the spaces out of the sheet names...makes for easier coding.

    Now when an items quanity hits 0 it will automatically be copied to Past Sales and deleted from Current Stock. If you don't want it deleted you can just comment out or delete that line.

    Test File: FNMoveBetweenSheets.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Ferenc Nagy (2015-05-14)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    Nice code!

    ..you could use almost the same routine, pasted into the code sheet for the [PastSales] sheet, to 'return' any record rows back to sheet [Current stock] if their rngCell.Value > 0
    For example, if you discover some hidden stock in a forgotten cupboard, just enter the amount on the [PastSales] sheet and it would then be sent back to the bottom of the [Current stock] sheet.

    zeddy

  5. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Followup

    That code works like a charm. Thank you, thank you, thank you!

    I have one follow up question: Once the line gets copied over to "PastSales", how can I automate to sort the now updated list alphabetically?

    And a follow up to my follow up... Can the alphabetical sorting be done while/if quantity would be the first column?

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ference,

    I assume you're talking about sorting the PastSales sheet? If so, all the quantities will be 0 since that is how they got there, or am I missing something?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ference,

    I assume you're talking about sorting the PastSales sheet? If so, all the quantities will be 0 since that is how they got there, or am I missing something?
    Using names from your own example sheet:

    "Paper" was already in "PastSales", next "Lizard" sold out and gets copied over to "PastSales". Now we have items "Paper" and "Lizard" in the list. Can it be automatically sorted in alphabetical order after Lizard appears in the list?

    One more: How can I set so At the time of copying any new item to "PastSales" the days date be entered into "Column E"?

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ference,

    Here's the Updated Worksheet_Change routine:
    Code:
    Option Explicit
    
    Sub SortPS(shtCS As Worksheet, shtPS As Worksheet)
    
       Dim lLastRow As Long
       Dim lLastCol As Long
    
       shtPS.Activate
       [A1].Select
       lLastRow = Cells(1, 1).End(xlDown).Row()
       lLastCol = Cells(1, 1).End(xlToRight).Column()
             
       With shtPS.Sort
           .SortFields.Clear
           .SortFields.Add Key:=Range("A2"), _
              SortOn:=xlSortOnValues, _
              Order:=xlAscending, _
              DataOption:=xlSortNormal
           .SetRange Range("A1:" & Cells(lLastRow, lLastCol).Address)
           .Header = xlYes
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
        End With '*** shtPS.Sort ***
             
        shtCS.Activate
    
    End Sub   '*** SortPS ***
    Here's the sort routine (place in standard module):

    Code:
    Sub Worksheet_Change(ByVal Target As Excel.Range)
    
       Dim rngCell    As Range
       Dim shtCS      As Worksheet
       Dim shtPS      As Worksheet
       Dim lTargetRow As Long
       
       Set rngCell = Intersect(Target, Range("B:B"))
       
       If Not rngCell Is Nothing Then
       
         Application.ScreenUpdating = False
         Set shtCS = Worksheets("CurrentStock")
         Set shtPS = Worksheets("PastSales")
         
         If rngCell.Count = 1 Then   'Prevents multiple selection
           Application.EnableEvents = False
           If rngCell.Value = 0 Then
           
             lTargetRow = shtPS.Cells(Rows.Count, 1).End(xlUp).Row() + 1
             
             rngCell.EntireRow.Copy Destination:=shtPS.Cells(lTargetRow, 1)
             Application.CutCopyMode = False
             rngCell.EntireRow.Delete
             
             SortPS shtCS, shtPS
             
           End If   'rngcell.value = 0
           
         End If  'rngCell.Count
         
       End If
    
       Set rngCell = Nothing
       Set shtPS = Nothing
       Application.EnableEvents = True
       Application.ScreenUpdating = True
       
    End Sub    'Worksheet_Change()
    Test File: FNMoveBetweenSheets.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2015-05-15)

  10. #8
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I managed to make "CurrentStock" sort itself alphabetically also by adding some extra code based on yours to sort "PastSales".

    Code:
    Sub SortPS(shtCS As Worksheet, shtPS As Worksheet)
    
       Dim lLastRow As Long
       Dim lLastCol As Long
    
    
       shtPS.Activate
       [A1].Select
       lLastRow = Cells(1, 1).End(xlDown).Row()
       lLastCol = Cells(1, 1).End(xlToRight).Column()
             
       With shtPS.Sort
           .SortFields.Clear
           .SortFields.Add Key:=Range("B2"), _
              SortOn:=xlSortOnValues, _
              Order:=xlAscending, _
              DataOption:=xlSortNormal
           .SetRange Range("A1:" & Cells(lLastRow, lLastCol).Address)
           .Header = xlYes
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
        End With '*** shtPS.Sort ***
             
        shtCS.Activate
       [A1].Select
       lLastRow = Cells(1, 1).End(xlDown).Row()
       lLastCol = Cells(1, 1).End(xlToRight).Column()
    
    
       With shtCS.Sort
           .SortFields.Clear
           .SortFields.Add Key:=Range("B2"), _
              SortOn:=xlSortOnValues, _
              Order:=xlAscending, _
              DataOption:=xlSortNormal
           .SetRange Range("A1:" & Cells(lLastRow, lLastCol).Address)
           .Header = xlYes
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
        End With '*** shtPS.Sort ***
    
    
    
    
    End Sub   '*** SortPS ***
    I could not figure out why I can't make the same code work if I start booth sheets at B1 instead of A1...

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    You only really need to 'sort' the data when you switch to a sheet.
    If you were updating several records to zero stock, you don't need to have the [PastSales] sorted each and every time you set a stock value to zero.
    So in my example file attached, I added a simple sheet event routine that sorts the data only when you view the sheet:
    Code:
    Private Sub Worksheet_Activate()
    [a1].Sort key1:=[a1], Header:=xlYes, Order1:=xlAscending
    End Sub
    I added some more sample records to demonstrate the sorting.

    One more: How can I set so At the time of copying any new item to "PastSales" the days date be entered into "Column E"?
    I also updated RG's code to add the date value (i.e when the record was 'moved' to the [PastSales] sheet).

    I also added the code to the [PastSales] sheet so that, if you change a stock value from zero to say, 10, then it will be 'moved' back to the [CurrentStock] sheet (and will appear in the correct 'sorted' position when you switch back to sheet [CurrentStock] )

    I also fixed the typo for the Quantity header cell in RG's file, which I used as the basis for my file.
    RG: you are mighty indeed! And I'm sure you put that in to test my eyes again! I thank you for that! (I'll let someone else give you your well-deserved 1000th!)

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-05-15 at 09:21.

  12. #10
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    It is chewing bubble gum and taking names now
    Can't thank you enough both of you for taking the time!

    What would I need to change to make it work from a different starting position? For example "Column A" and "Row 1" I want to keep empty.

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    ..not sure I understand you.
    Are you saying cell [A1] is empty, but cells [B1] , [C1], [D1] still have heading labels??

    I have attached another sample file, where the headings row starts in row 10
    You can make any changes in the top section rows.

    zeddy
    Attached Files Attached Files

  14. #12
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    This was what I was trying to do yes, except now there is a problem with the macro. Every time a row is being copied into either sheet it is inserted into the 2nd row and keeps overwriting anything that is there instead of entering the last row of the sheet. Try to turn quantity to zero on CurrentStock a few times then check PastSales and you will see what I am talking about.

    Thanks for taking a look.

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Ferenc

    ..when I use the file as posted in post#11, it works as planned i.e. rows are copied to the bottom of the PastSales sheet, and then, when you then switch to the PastSales sheet, the autosort occurs and those records are in their sorted order. Ditto, when you 'post' records back to the CurrentStock sheet by changing the zero value to a positive number.

    Please check again, with the file in post#11, and report back.

    zeddy

  16. #14
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I just realized the error started happening after I moved the columns to the right by 1. Could you please check out what I did wrong please.
    Attached Files Attached Files

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..you did most of it (well done!), but needed to also fix
    'updated..
    lTargetRow = shtPS.Cells(Rows.Count, "B").End(xlUp).Row() + 1

    see attached file

    zeddy
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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