Results 1 to 4 of 4

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

Threaded View

Vanessavalentino83 Need VBA to split data into... 11-25-2024, 12:37 PM
6StringJazzer Re: Need VBA to split data... 11-25-2024, 01:14 PM
Vanessavalentino83 Re: Need VBA to split data... 11-26-2024, 06:56 AM
6StringJazzer Re: Need VBA to split data... 11-26-2024, 10:38 AM
  1. #1
    Registered User
    Join Date
    11-25-2024
    Location
    US
    MS-Off Ver
    2017
    Posts
    2

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

    Cross-posted at https://www.mrexcel.com/board/thread...ation.1267167/

    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
    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
    Last edited by 6StringJazzer; 11-25-2024 at 01:14 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to Split data into multiple sheets
    By Bimmer318i in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2021, 07:08 AM
  2. View Data from multiple sheets to just one sheet by Data validation
    By markusvirus in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2017, 01:35 AM
  3. [SOLVED] Split Data from one sheet into multiple sheets
    By jstewart22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-11-2013, 06:54 PM
  4. [SOLVED] Data Validation - replace choices for multiple sheets of source data
    By mshaw1970 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2013, 07:34 AM
  5. [SOLVED] Split data in one worksheets into multiple sheets?
    By cahillct in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-14-2012, 07:51 AM
  6. [SOLVED] Question About IF/THEN with Data Validation AND multiple cell data paste
    By erynatpitt in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-10-2012, 03:57 PM
  7. Split Data to multiple Sheets
    By georgeanaprop in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2010, 08:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1