HOW TO INSERT SECTION IN VBA TO --NOT-- HARDCODE FORMULA IN CREATED TABS

[JuicyMusic]

Hello again,

I have an existing vba code that works very well. I just want to add something to it.

When this code is run it will create multiple tabs - based on what is on the active sheet - and based on the data in whatever column I choose.
Example: If I choose column A with employee names - it will create a tab for each employee.

NOTE: The only column in the "main" tab that has a formula in it in Column J. Data from Column A thru M will be data entry work only.

How can I have the formula active in all the new tabs in column J? Thank you so much!

Sub Split_Data_Into_Tabs()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    'This macro splits data into multiple worksheets based on the variables on a column found in Excel.
    'An InputBox asks you which columns you'd like to filter by, and it just creates these worksheets.
    Application.ScreenUpdating = False
    vcol = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Default:="3", Type:=1)
    Set ws = ActiveSheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
        On Error Resume Next
        If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
            ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
        End If
    Next
    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear
    For i = 2 To UBound(myarr)
        ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
        If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
            Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
        Else
            Sheets(myarr(i) & "").Move After:=Worksheets(Worksheets.Count)
        End If
        ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
        'Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    Application.ScreenUpdating = True
End Sub

 

[Zot]

If you want to fill formula to a range like J1 to J100, then you just need to add line:

Range("J1","J100").Formula = "= <whatever formula>"

This will fill the range accordingly.

I hope I understood your question right. ?

 

[JuicyMusic]

Hello Zot, Please excuse my delay in responding. I've been working on a different project and now I'm back to this.

I'm going to try that right now. Thank you.