+ Reply to Thread
Results 1 to 18 of 18

VBA code to create table auto-fill with data from another tab based on a condition

  1. #1
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    VBA code to create table auto-fill with data from another tab based on a condition

    Hi all,

    I am new to VBA and i am a bit lost on a task. I need to create a table tab in the same file where i have the data, a table which will contain a only a few information (country, analysts, etc.) per project with data from a master tab. All the data is in this master tab, and i would need a VBA code to help me take some of the information i need from the this master data tab, and create a table with 5-6 projects - The table ideally will auto-fill with the necessary data as soon as i select the project name for each column. The difficulty is that while the information about each project is numerous and not in any particular order - so for the table, i would need to identify specific data (region, country, analyst, etc.) and place the information in my created table.

    i am attaching a sample of the simplified excel - i hope this is clear. I would be extremely thankful for your help!!!

    Thank you,
    kind regards,
    CM
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Can you attach a copy of your file which contains your expected results in Tab 2. Explain in detail how you got the results referring to specific cells, rows, columns and sheets using a few examples form your data.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Hi Mumps1, thank you very much for your reply. I just added how the output should look like in Tab2. I have done it with a formula index/match because I am not sure on even how to start to do it in VBA.

    I added a legend in the file, I hope is helpful:
    - the data tab has a lot of columns and rows in reality (about 400 r and c), including a column (F) that contains the name of "entities" that need to become the header columns of my table : i think this is easier than having the header columns of the tab being chose by a drop down list
    - in terms of output of table, the rows of my table should be the columns that I have in the data tab, however only a selection of them (about 100 of the 400 columns of the data tab)



    Thank YOU very much.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Maybe that's what you expect.
    Please Login or Register  to view this content.
    Last edited by maras_mak; 01-11-2021 at 12:31 AM. Reason: Code correction.
    Best Regards,
    Maras.

  5. #5
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Maras - Thank YOU! This works perfectly! much obliged

  6. #6
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Glad it worked. Tx for rep.

  7. #7
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Hi Maras, may i bother you with one more question? I tried to add the rest of my headers in the array function (i have around 120 so basically they dont fit into one line), and I get a "compile error: expected end of statement". Is there a limit to the numbers of elements i can add into the array?

    Many thank you!

  8. #8
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    from webside:
    "the maximum size of a VBA array depends on 2 main factors:
    Your operating system.
    Available memory.
    As a general rule, execution is slower whenever you use an array whose size exceeds the RAM memory that's available in the system you're working with.
    This is because, as explained by Microsoft, “the data must be read from and written to disk”."
    https://powerspreadsheets.com/excel-...y-Requirements

    Only some of the variable declarations contain changes, the rest remain unchanged.
    HTML Code: 
    Check if it works well.
    Last edited by maras_mak; 01-14-2021 at 10:05 AM.

  9. #9
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Hi Maras, I appreciate the explanation. I think it should work doing it this way - but for some reason, when applying this variable definition, now the headers are not working = they appear as "N/A". Have you encountered this before?


    Attachment 713398

  10. #10
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    I can check if there is an attachment with this problem.

  11. #11
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Hi Maras, sure - I tried with this example, please see below. Again, thank you very much for your time.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    You entered 'delimiter' incorrectly. There must be no spaces!
    change
    hdrs = Split (HD, ", ")
    on
    hdrs = Split (HD, ",") ' no space after comma

  13. #13
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    UGH!!! Thank you for checking such a silly thing nevertheless..I confirm it is working perfectly and you a rock star. Many thank you for your patience and help!

  14. #14
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Hi Maras, it is me again, if you still have the patience to help me. Your macro works beautifully and i managed to adjust it to the full list of data that i had. However I encounter the following error: when the data tab, from where i source the data for tranpose, is empty, then i get an error.

    I created a button that links basically the search functionality for this database with my table (your macro), so that everytime i press the button, the search is done and my table gets filled with the tranposed information. So now, if the data tab is empty because the search led to no results, i get a VBA error. I tried to bypass this by coding the below macro for the button (please dont make fun of my limited skills!):

    Sub Button1_Click()

    With Sheets("Data").Range("A2:PV2")
    If IsEmpty(.Range("A2:pv2")) Then
    MsgBox ("Your search returned no results. Please refine the criteria.")

    Exit Sub
    End If
    End With

    Call Intro
    Call Macro2

    Sheets("Reporting").Activate

    End Sub


    However the error goes back to the original macro and notably to this part below:

    'Select the columns from "Data" tab to become part of the reporting table:
    With Sheets("Data").[a1].CurrentRegion

    a = .Offset(1).Resize(.Rows.Count - 1).Value
    cls1 = Application.Match(hdrs1, .Rows(1), 0)
    v1 = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), cls1)

    End With


    Would you have a quick fix? The error is similar to the one in the excel file shared.

    Thank you, yet cant thank you enough!
    AC

  15. #15
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    The code looks fine, but there are questions:
    1 / How do you create 'hdrs1',
    2 / what does 'a' look like - is an array?
    Without an attachment, it's hard to guess.
    Maybe something like that.
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    Hi Maras, you are right please see attached. I included this if condition with a MsgBox "redefine criteria" if the data in "Data" tab is empty - however the transpose macro has an issue with this.

    Thank you!
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    A little change should help.
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    01-09-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    10

    Re: VBA code to create table auto-fill with data from another tab based on a condition

    thank you a lot, it works indeed thanks to that detail... Thanks for taking the time!

+ 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] Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table
    By ITY in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-23-2019, 09:57 AM
  2. How do I create a table which will auto fill if the criteria are met ?
    By Darksiedluv in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2019, 02:38 AM
  3. Replies: 1
    Last Post: 03-09-2017, 12:13 AM
  4. Fill arrays from sheet / SUMIFS / create & fill table based on multiple criteria
    By Dimitris254 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-03-2016, 09:43 AM
  5. Create a auto fill table with unlimited length
    By smahler in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-03-2014, 03:07 PM
  6. data auto fill base on condition.
    By johncena in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2013, 04:30 PM
  7. Auto fill table based on the data from another workbook
    By Biona in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2011, 01:30 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