Need VBA to split data into multiple sheets paste formulas, formatting AND data validation

[vanessavalentino83]

Hello!

I am using the following VBA code to split data into multiple sheets based on a value in a column of the "Master sheet". I need it to paste formulas, formatting AND data validation into the newly created sheets. However; the existing code is only pasting/copying formulas and formatting, NOT data validation.

I tried adding .PasteSpecial xlPasteValidation after .PasteSpecial xlPasteFormats but that didn't work, it caused the new worksheets to all be blank.


can anyone help please?

Sub parse_data_MT_VERSION()
    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

    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
With Sheets(myarr(i) & "").Range("A1")
.PasteSpecial xlPasteFormulas
.PasteSpecial xlPasteFormats
.Columns.AutoFit
        End With
        ActiveSheet.AutoFilter.ShowAllData
'Hide Columns D,E
Columns("B:I").EntireColumn.Hidden = True
Columns("P:T").EntireColumn.Hidden = True
Columns("Y:AI").EntireColumn.Hidden = True
Columns("AM:AN").EntireColumn.Hidden = True
Columns("AS:DJ").EntireColumn.Hidden = True
    Next

    ws.AutoFilterMode = False
    ws.Activate
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need VBA to split data into multiple sheets paste formulas, formatting AND data validation
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[vanessavalentino83]

I figured it out! I just needed to add .PasteSpecial xlPasteValidation BEFORE .PasteSpecial xlPasteFormulas
thanks anyway!