Micro for splitting by 1 column into multiple tabs on the same wordbook

KellieMeehan

New Member
Joined
Jan 19, 2022
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have an excel spreadsheet and would like a micro for splitting the data into multiple tabs using the data in just 1 column and then for the macro to name the tab by what is in the splitting column, hope this makes sense, I would like the column in yellow to be the column that splits the data

1643644356687.png
 

Attachments

  • 1643644172379.png
    1643644172379.png
    75.3 KB · Views: 10

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try:
VBA Code:
Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, ws As Worksheet
    Set ws = Sheets("Sheet1")
    v = ws.Range("K2", ws.Range("K" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        With ws
            .Range("A1").CurrentRegion.AutoFilter 11, v(i, 1)
            .AutoFilter.Range.Copy
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = v(i, 1)
            Range("A1").PasteSpecial
        End With
    Next i
    ws.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you, it worked on the example spreadsheet, how can I get this to work on other spreadsheets, using a different column, can as box be added that pops up asking which column the data needs to be split by?
 
Upvote 0
Try:
VBA Code:
Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, ws As Worksheet
    Set ws = Sheets("Sheet1")
    v = ws.Range("K2", ws.Range("K" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        With ws
            .Range("A1").CurrentRegion.AutoFilter 11, v(i, 1)
            .AutoFilter.Range.Copy
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = v(i, 1)
            Range("A1").PasteSpecial
        End With
    Next i
    ws.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
Thank you, it worked on the example spreadsheet, how can I get this to work on other spreadsheets, using a different column, can as box be added that pops up asking which column the data needs to be split by?
 
Upvote 0
Try:
VBA Code:
Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, ws As Worksheet, col As String
    col = InputBox("Please enter the column letter.")
    If col = "" Then Exit Sub
    Set ws = Sheets("Sheet1")
    v = ws.Range(col & "2", ws.Range(col & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        With ws
            .Range("A1").CurrentRegion.AutoFilter 11, v(i, 1)
            .AutoFilter.Range.Copy
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = v(i, 1)
            Range("A1").PasteSpecial
        End With
    Next i
    ws.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try:
VBA Code:
Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, ws As Worksheet, col As String
    col = InputBox("Please enter the column letter.")
    If col = "" Then Exit Sub
    Set ws = Sheets("Sheet1")
    v = ws.Range(col & "2", ws.Range(col & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        With ws
            .Range("A1").CurrentRegion.AutoFilter 11, v(i, 1)
            .AutoFilter.Range.Copy
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = v(i, 1)
            Range("A1").PasteSpecial
        End With
    Next i
    ws.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
thank you, I am getting the following debug message when I'm trying to run this on another spreadsheet

1643726669873.png
 
Upvote 0
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture as in your original post) of your sheet. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. What is the actual error message you are getting?
 
Upvote 0
I can't use the free sites, the firewall blocks it, I just want a macro that I can run on different spreadsheets that I can dictate which column the data is split by into different tabs :(
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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