Transpose then sort

[Babynod]

Hi All,
i currently use a code to tranpose data from Column B Data Entry (sheet1) to Database (sheet 2) using a cmdbutton

Private Sub CmbSubmit_Click()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    
    Set srcSht = Worksheets("Data Entry")
    Set destSht = Worksheets("Database")
    With destSht
        Set destRng = .Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
    
    With srcSht
        Set srcRng = .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
    End With
    
    srcRng.Copy
    destRng.PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    Application.CutCopyMode = False
    
    Range("B:B").ClearContents

End Sub

Once its on sheet 2 and has been pasted i want it to autosort based on a custom sort order (this works by itself just not with the VBA above) which refeshes when data is manualy entered into the columns on sheet 2
using

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

how can i get this custom sort to sort when the data is inserted from the cmdbutton on sheet 1?

 

[jolivanes]

AFAIK, copy/paste with a macro does not trigger a Worksheet_Change event.

Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet, nr As Long, lr As Long
Set sh1 = Worksheets("Data Entry")
Set sh2 = Worksheets("Database")
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
nr = sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    sh2.Cells(nr, 1).Resize(, lr).Value = Application.Transpose(sh1.Range("B1:B" & lr).Value)
If Len(sh2.Cells(nr, 1)) <> 0 Then    'Call your Filter macro here
End Sub

With this you don't need a Worksheet_Change event

Events In Excel VBA

www.cpearson.com

 

[Babynod]

Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet, nr As Long, lr As Long
Set sh1 = Worksheets("Data Entry")
Set sh2 = Worksheets("Database")
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
nr = sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1
sh2.Cells(nr, 1).Resize(, lr).Value = Application.Transpose(sh1.Range("B1:B" & lr).Value)
If Len(sh2.Cells(nr, 1)) <> 0 Then 'Call your Filter macro here
End Sub

Where would i put this one? in the module or worksheet?

 

[jolivanes]

Regular module

 

[Babynod]

Regular module

works mint, thanks mate

 

[jolivanes]

Thanks for letting us know and good luck.