sort cut paste macro

[Tonyk1051]

Hi,

i tried recording a macro of me just sorting cutting and pasting...didnt quite work well (the cut and paste part) when i tested the macro out -__-

There will be text in column H, (in tab1) i go to data, check the box to use my headers and sort by column H which brings up everything to the top, any line that has a text/anything at all in column H, i cut and paste to line A2 to tab 4 (cat tab) and then delete the empty space from tab 1

when i repeat the process, the 2nd batch i try to cut and paste over to tab 4, it covers the original and brings in lines that dont have anything in column H...which i dont want. so for example lets say i did cut and paste to tab 4, 20 lines. the 2nd batch should be pasted onto line A21 not line A2

the amount of data always varies, sometimes theres 2 lines that have something in column H sometimes its a couple 100...
sorry if this is confusing


tester.xlsm tester file

 

[Alex Blakenburg]

I am unclear as to what needs to happen to the data in the original sheet.
ie Cut and Paste indicates those rows should be removed but it sounds like you may not want the other remaining rows either.
I have given 2 options towards the end of the code but both are currently commented out.

On a copy of your workbook, see if this does what you are after.
PS: your cat sheet has an additional blank column at column Y, I have assumed that is an error and ignored it.

Sub CopyPaste()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcLastRow As Long, destLastRow As Long, srcLastCopyRow As Long
    Dim srcLastCol As Long
    Dim srcRng As Range, destRng As Range, srcCopyRng As Range
   
   
    Set srcSht = Worksheets("Not on a Category")
    With srcSht
        srcLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        If srcLastRow = 1 Then
            MsgBox "No Date in Source Sheet"
            Exit Sub
        End If
       
        srcLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set srcRng = .Range(.Cells(1, "A"), .Cells(srcLastRow, srcLastCol))
    End With
   
    Set destSht = Worksheets("cat")
    With destSht
        destLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set destRng = .Range("A" & destLastRow)
    End With
   
    Application.ScreenUpdating = False
   
    srcSht.Sort.SortFields.Clear
    srcRng.Sort Key1:=srcSht.Range("H2"), _
         Order1:=xlAscending, Header:=xlYes
    srcLastCopyRow = srcRng.Cells(srcRng.Rows.Count, "H").End(xlUp).Row
   
    If srcLastCopyRow = 1 Then
        MsgBox "Nothing to Copy"
        GoTo CleanExit
    End If
    Set srcCopyRng = srcRng.Resize(srcLastCopyRow - 1).Offset(1)
   
    srcCopyRng.Copy Destination:=destRng.Offset(1)
   
   
    ' XXX Optional determine what to do with initial data
    ' Option 1 - Delete copied rows
    ' srcCopyRng.EntireRow.Delete
   
    ' Option 2 - Clear all data
    ' srcRng.Offset(1).ClearContents

CleanExit:
    Application.ScreenUpdating = True

End Sub

 

[Tonyk1051]

I am unclear as to what needs to happen to the data in the original sheet.
ie Cut and Paste indicates those rows should be removed but it sounds like you may not want the other remaining rows either.
I have given 2 options towards the end of the code but both are currently commented out.

On a copy of your workbook, see if this does what you are after.
PS: your cat sheet has an additional blank column at column Y, I have assumed that is an error and ignored it.

Sub CopyPaste()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcLastRow As Long, destLastRow As Long, srcLastCopyRow As Long
    Dim srcLastCol As Long
    Dim srcRng As Range, destRng As Range, srcCopyRng As Range
  
  
    Set srcSht = Worksheets("Not on a Category")
    With srcSht
        srcLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        If srcLastRow = 1 Then
            MsgBox "No Date in Source Sheet"
            Exit Sub
        End If
      
        srcLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set srcRng = .Range(.Cells(1, "A"), .Cells(srcLastRow, srcLastCol))
    End With
  
    Set destSht = Worksheets("cat")
    With destSht
        destLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set destRng = .Range("A" & destLastRow)
    End With
  
    Application.ScreenUpdating = False
  
    srcSht.Sort.SortFields.Clear
    srcRng.Sort Key1:=srcSht.Range("H2"), _
         Order1:=xlAscending, Header:=xlYes
    srcLastCopyRow = srcRng.Cells(srcRng.Rows.Count, "H").End(xlUp).Row
  
    If srcLastCopyRow = 1 Then
        MsgBox "Nothing to Copy"
        GoTo CleanExit
    End If
    Set srcCopyRng = srcRng.Resize(srcLastCopyRow - 1).Offset(1)
  
    srcCopyRng.Copy Destination:=destRng.Offset(1)
  
  
    ' XXX Optional determine what to do with initial data
    ' Option 1 - Delete copied rows
    ' srcCopyRng.EntireRow.Delete
  
    ' Option 2 - Clear all data
    ' srcRng.Offset(1).ClearContents

CleanExit:
    Application.ScreenUpdating = True

End Sub

Hi, yes column y is mistake, apologies i should have deleted that. i tried both codes, they both do as intended except for one thing...it didnt cut and paste, it copied the data. once the data is cut and pasted to cat tab i manually go back to tab one and delete the empty space/lines

 

[Tonyk1051]

technically i dont need cut and paste, i just need to make sure there no duplicate data in the workback. so if the macro is just copy and pasting the data to cat tab. so long as what is copied in tab one is deleted and the copy of it is in cat tab, thats fine also. whichever makes it easier for you to create

 

[Alex Blakenburg]

Just uncomment option 1. EntireRow.Delete

 

[Tonyk1051]

Just uncomment option 1. EntireRow.Delete

thanks Alex

 

[Alex Blakenburg]

You're welcome. Glad I could help.