Results 1 to 7 of 7

Distributing data into varying tabs VBA

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-19-2013
    Location
    Las Vegas, NV
    MS-Off Ver
    Office 365 2016
    Posts
    214

    Distributing data into varying tabs VBA

    Hello all,

    Been a little while, but am back with a VBA question. I copied a generic VBA code (https://yodalearning.com/tutorials/e...to-split-data/) to distribute information from the attached sample file's "TestTab" using the 7th column's field "AssetClass" to new tabs per each "AssetClass" type.

    I tried to modify the codee for my file as best I could but am getting stuck at the point where it filters and attempts to create a new sheet
    rData.Copy Destination:=Worksheets(assetclass).Cells(1, 1)
    Below is the full code

    Sub ExtractToSheets()
        Dim ws          As Worksheet
        Dim wsNew       As Worksheet
        Dim rData       As Range
        Dim rfl         As Range
        Dim assetclass  As String
        Set ws = ThisWorkbook.Sheets("testtab")
    
        With ws
            Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 7).End(xlUp))
            .Columns(.Columns.Count).Clear
            .Range(.Cells(2, 7), .Cells(.Rows.Count, 7).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
        
        'Calls Excel Advanced Filter
        
            For Each rfl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))
                assetclass = rfl.Text
        
                rData.AutoFilter Field:=7, Criteria1:=assetclass
                rData.Copy Destination:=Worksheets(assetclass).Cells(1, 1)
            Next rfl
        End With
        ws.Columns(Columns.Count).ClearContents
        rData.AutoFilter
    End Sub
    Also on the included sample file are three green tabs showing the expected results. Thank you for any help.
    Kondukt
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formulas on Mutliple Tabs With Varying Rows of Data
    By spyldbrat in forum Excel General
    Replies: 2
    Last Post: 08-02-2017, 05:51 PM
  2. Need help with a VBA code for distributing data
    By cspeid03 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-22-2016, 01:40 AM
  3. [SOLVED] Formula to correlate varying data in one column to varying data in another column
    By Chris_SS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2014, 07:36 AM
  4. Distributing values or data with a group???
    By FifiNguyen in forum Excel General
    Replies: 0
    Last Post: 03-01-2014, 03:46 AM
  5. [SOLVED] Distributing Data from an expanding worksheet list and appending to various tabs in the sa
    By Cdot in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2013, 09:06 PM
  6. Pulling large amounts of data from varying tabs
    By bjvick in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-14-2010, 05:24 PM
  7. Distributing Data
    By Milnet in forum Excel General
    Replies: 6
    Last Post: 08-10-2009, 02:55 AM

Tags for this Thread

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