How to Change Where A Macro is looking to Create Tabs

GCLIFTON

Board Regular
Joined
Feb 11, 2016
Messages
60
Need your help. Currently this Macro is looking in A2 to Filter and create Tabs. How can i change this to look in Column U and do the same thing.

Code:
Sub Tab_()

    Application.ScreenUpdating = False
    Dim ws1 As Worksheet
    Set ws1 = ActiveSheet
    Dim bottomA As Long
    bottomA = ws1.Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim rng As Range
    Dim ws As Worksheet
    ws1.Range("A1:A" & bottomA).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("A1:A" & bottomA), Unique:=True
    Set rngUniques = ws1.Range("A2:A" & bottomA).SpecialCells(xlCellTypeVisible)
    If ws1.FilterMode Then ws1.ShowAllData
    For Each c In rngUniques
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(CStr(c.Value))
        On Error GoTo 0
        If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = CStr(c.Value)
                ws1.Rows(1).EntireRow.Copy ActiveSheet.Cells(1, 1)
        End If
    Next c
    For Each rng In rngUniques
        Sheets(CStr(rng)).UsedRange.Offset(1, 0).ClearContents
        ws1.Range("A1:I" & bottomA).AutoFilter Field:=1, Criteria1:=rng
        ws1.Range("A2:I" & bottomA).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(CStr(rng)).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        If ws1.FilterMode Then ws1.ShowAllData
    Next rng
    ws1.Activate
    Application.ScreenUpdating = True
 End Sub
 
Last edited by a moderator:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top