+ Reply to Thread
Results 1 to 6 of 6

Pull data from 1 table into separate tabs

  1. #1
    Registered User
    Join Date
    03-08-2015
    Location
    Retford
    MS-Off Ver
    2011
    Posts
    8

    Pull data from 1 table into separate tabs

    Hi guys,

    I have a tab set up with specific data in columns

    In column A are region codes, in column B are store codes and then the rest of the sheet is specific data..

    I want to extract this into a separate tab for all region codes and populate the rest of the data as it is now including the formatting and column headers just for that code in that tab - youll see the tabs for region codes in the sheet that are empty..

    Any way to do this quickly?

    Ill attach a sheet for reference..

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,438

    Re: Pull data from 1 table into separate tabs

    This is differant approach.

    I used this code to re-arange (a part of) your file. See the sheet Oeldere.

    Please Login or Register  to view this content.
    After that I made a table of the data in sheet Output => insert => table

    After that I made a pivot table is sheet PT Oeldere => insert => pivot table.

    The pivot table is made for the high risk, but can also easely changed to all criteria.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: Pull data from 1 table into separate tabs

    There is a VBA solution for this in the tutorial here: https://www.extendoffice.com/documen...on-column.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Registered User
    Join Date
    03-08-2015
    Location
    Retford
    MS-Off Ver
    2011
    Posts
    8

    Re: Pull data from 1 table into separate tabs

    Thanks - the above from extend office broadly works, it doesnt pull across the column headers and doesnt keep the same cell widths as per the 'master sheet".

    Can anyone help with modifying this code to do what i want to achieve as its nearly there - as in replicate the master file exactly but extract by the region into separate tabs:

    Heres the code from extend office

    Sub Splitdatabycol()
    'updateby Extendoffice
    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
    Dim xTRg As Range
    Dim xVRg As Range
    Dim xWSTRg As Worksheet
    On Error Resume Next
    Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
    If TypeName(xTRg) = "Nothing" Then Exit Sub
    Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
    If TypeName(xVRg) = "Nothing" Then Exit Sub
    vcol = xVRg.Column
    Set ws = xTRg.Worksheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = xTRg.AddressLocal
    titlerow = xTRg.Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    Application.DisplayAlerts = False
    If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
    Else
    Sheets("xTRgWs_Sheet").Delete
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
    End If
    Set xWSTRg = Sheets("xTRgWs_Sheet")
    xTRg.Copy
    xWSTRg.Paste Destination:=xWSTRg.Range("A1")
    ws.Activate
    For i = (titlerow + xTRg.Rows.Count) 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
    xWSTRg.Range(title).Copy
    Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
    ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
    Sheets(myarr(i) & "").Columns.AutoFit
    Next
    xWSTRg.Delete
    ws.AutoFilterMode = False
    ws.Activate
    Application.DisplayAlerts = True
    End Sub

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,438

    Re: Pull data from 1 table into separate tabs

    @jamesw6810

    Since you got answers of several forummembers it is good to add to whom you replying.

    Please add the code between # according to the forumrules.

    Please also reply on my offered solution.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: Pull data from 1 table into separate tabs

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Consolidating data from multiple tabs into one continuous table on separate sheet
    By moneypennie21 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-14-2018, 05:25 AM
  2. Excel VBA to pull minimum and maximum date in separate table
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2015, 10:52 PM
  3. Moving multiple excel tabs into separate tabs/separate files
    By RO24 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-02-2014, 11:04 AM
  4. Macro to transfer data submitted on monthly tabs into a table on a separate tab
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2014, 09:31 PM
  5. [SOLVED] Pull data every table across all tabs
    By benji912 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-24-2014, 09:06 PM
  6. Pull information into separate tabs
    By kmlloyd in forum Excel General
    Replies: 9
    Last Post: 09-27-2010, 10:56 AM
  7. Pull data from separate tabs into one tab
    By azreil in forum Excel General
    Replies: 12
    Last Post: 06-14-2010, 10:28 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