Running macro on first two sheets

[plfas]

So I have a macro that imports data (responses from a survey in French and English) from two closed workbooks into sheets 1 and 2 (named "English" and "French", respectively).

I then have a second macro that splits the timestamp (column A) into two columns to separate the date from the time.
It then checks column 6 (F) to see if it is "Abandoned" and deletes that row.
It then adds a formula in T3 and U3 and autofills dynamically to the end of the column

Right now this macro only runs on the first sheet. I want it to run on the first and second sheet. I would appreciate any optimizations as well as it is a mish-mash of code I found searching forums.

Sub SplitDate()

'
' Splits Date & Time into two columns
'
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A:A").TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    
'Deletes responses abandoned on first question


Dim LastRow As Long


LastRow = [A65536].End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, 6) = "Abandoned" Then Rows(i & ":" & i).EntireRow.Delete
Application.ScreenUpdating = False
Next i


'' Adds Promoters,Detractors, etc to columns t/u


Dim endrow As Long


endrow = Sheets("English").UsedRange.SpecialCells(xlCellTypeLastCell).Row


Range("T3").FormulaR1C1 = _
        "=IF(RC[-13]=""Unattempted"",""N/A"",IF(RC[-13]=""Abandoned"",""N/a"",IF(RC[-13]>8,""Promoter"",IF(RC[-13]>6,""Passive"",""Detractor""))))"
Range("T3").AutoFill Destination:=Sheets("English").Range("T3:T" & endrow), Type:=xlFillDefault


Range("U3").FormulaR1C1 = _
        "=IF(RC[-10]=""Unattempted"",""N/A"",IF(RC[-10]=""Abandoned"",""N/a"",IF(RC[-10]>8,""Promoter"",IF(RC[-10]>6,""Passive"",""Detractor""))))"
Range("U3").AutoFill Destination:=Sheets("English").Range("U3:U" & endrow), Type:=xlFillDefault
End Sub

 

[Darren Bartrup]

I haven't tested this:

Sub SplitDate()


    Dim wrkSht As Worksheet
    Dim i As Long
    
    For Each wrkSht In ThisWorkbook.Worksheets
        If wrkSht.Name = "English" Or wrkSht.Name = "French" Then
            With wrkSht


                '
                ' Splits Date & Time into two columns
                '
                    .Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Range("A:A").TextToColumns Destination:=.Range("A:A"), DataType:=xlDelimited, _
                        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
                        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
                    
                'Deletes responses abandoned on first question
                
                
                Dim LastRow As Long
                
                
                LastRow = .Range("A65536").End(xlUp).Row
                For i = LastRow To 1 Step -1
                If .Cells(i, 6) = "Abandoned" Then .Rows(i & ":" & i).EntireRow.Delete
                Application.ScreenUpdating = False
                Next i
                
                
                '' Adds Promoters,Detractors, etc to columns t/u
                
                
                Dim endrow As Long
                
                
                endrow = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
                
                
                .Range("T3").FormulaR1C1 = _
                        "=IF(RC[-13]=""Unattempted"",""N/A"",IF(RC[-13]=""Abandoned"",""N/a"",IF(RC[-13]>8,""Promoter"",IF(RC[-13]>6,""Passive"",""Detractor""))))"
                .Range("T3").AutoFill Destination:=.Range("T3:T" & endrow), Type:=xlFillDefault
                
                
                .Range("U3").FormulaR1C1 = _
                        "=IF(RC[-10]=""Unattempted"",""N/A"",IF(RC[-10]=""Abandoned"",""N/a"",IF(RC[-10]>8,""Promoter"",IF(RC[-10]>6,""Passive"",""Detractor""))))"
                .Range("U3").AutoFill Destination:=.Range("U3:U" & endrow), Type:=xlFillDefault
            End With
        End If


    Next wrkSht


End Sub

Each time you say Range(...) or Cell(....) it works on the activesheet, using the WITH keyword allows us to put a full-stop (period) in front of these range references - and each one will then apply to the worksheet in question. (Not very well explained - my phone just rang)...

 

[plfas]

That worked perfectly. Thanks a lot!

 

[Darren Bartrup]

No problem, happy to help.