I need each line commented in this code so I can update it

[dpaton05]

I don't know what each line of this code does. Can someone add a comment for each line so I can update it please?

Sub Transfer()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        
Application.ScreenUpdating = False
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt
                                .AutoFilter 3, Req
                                .Offset(1).EntireRow.Copy sht.Range("A" & Rows.Count).End(3)(2)
                                .Offset(1).EntireRow.Delete
                                .AutoFilter
                        End With
                End If
        Next ws
        
sh.Range("B25,B27").ClearContents
Application.ScreenUpdating = True

End Sub

 

[Michael M]

So, rather than comment EVERY line, why not tell us which ones you don't understand ?
This part, should be obvious

Sub Transfer()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        
Application.ScreenUpdating = False
        
        For Each ws In Worksheets

 

[dpaton05]

Ok, with these lines,

       Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value

....using [ and ], if you surround the cell reference, is this a short way of range("B25")?

I also don't understand the most of the for each loop.
With this line, I understand that it is asking if the worksheet name is different to Cancellations or Totals but that is all.

If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then

 

[Michael M]

Sub Transfer()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value 'shorter version of Range("B25")
        Dim Dt As String: Dt = sh.[B27].Value    'As above
        
Application.ScreenUpdating = False
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then 'any worksheet except the 2 mentioned
                        With ws.[A3].CurrentRegion 'the region or range of cells [A3] resides in
                                .AutoFilter 1, Dt ' autofilter for the value in cell [B27]
                                .AutoFilter 3, Req ' autofilter for the value in cell [B25]
                                .Offset(1).EntireRow.Copy sht.Range("A" & Rows.Count).End(3)(2) 'copy row to first available row on sht sheet
                                .Offset(1).EntireRow.Delete 'delete the row once copied
                                .AutoFilter ' turn off the autofilter
                        End With
                End If
        Next ws
        
sh.Range("B25,B27").ClearContents 'clear the contents of B25, B27 on the "Totals" Sheet
Application.ScreenUpdating = True

End Sub

 

[dpaton05]

I understand the autofilter feature looks for and finds rows that have the values in B25 and B27 in columns 1 and 3 but why does it have an offset of 1, as you are finding the cell? Does it need to include the header row?

Also, in the offset line of code, why is there a (3)(2) after the .end?

 

[Michael M]

Offset 1 is to avoid the header row and select the next row down
AND
Instead of

.Offset(1).EntireRow.Copy sht.Range("A" & Rows.Count).End(3)(2)

USE

.Offset(1).EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1)

which may make more sense to you

 

[dpaton05]

Thanks Michael

 

[Michael M]