+ Reply to Thread
Results 1 to 13 of 13

Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

  1. #1
    Registered User
    Join Date
    07-22-2019
    Location
    Israel
    MS-Off Ver
    Office 2016
    Posts
    22

    Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    Hi,

    I will appreciate your help in some simple action I want to do in VBA:

    I have a dynamic table with changing amount of columns and rows.

    For some of the columns I have data only in their first row (row#2) that I need to duplicate to the other rows of the columns (respectively)
    till last row of the table (reference can be column A).

    I'm trying to use the Auto Fill / Fill Down options (copy values type), but missing something to perform this action successfully.

    *** Comment: Since it's a dynamic table, I can't use fixed ranges like D2:H2 or D2:H.
    I need to use the Headers names of these columns, which are unique, as a reference.

    Attached is an example.

    Can you please recommend how to write a simple code for this need?

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    Use:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    Alternatively, format your source data as a table, then use Power Query (Get & Transform Data):

    Please Login or Register  to view this content.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,156

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    ..................deleted................
    Olly solved....
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Registered User
    Join Date
    07-22-2019
    Location
    Israel
    MS-Off Ver
    Office 2016
    Posts
    22

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    Thanks Olly for your quick response and solution, but it is not good for me... I must clarify that I have a lot of blank cells across all the table, here and there, and your macro fills them with not needed data and also with #REF.

    I need the AutoFill or FillDown macro to be activated ONLY on the columns I marked in yellow, which I should refer to them according to their unique names (Header#4 ---> Header #8), not their ranges because they change.
    and macro should refer to the 1st TEXT row (row#2) as the "MODEL" for duplication down to the empty cells, in each column respectively, till last row of the table.

    I hope it is clear now.

    Thanks.
    Last edited by ITY; 07-23-2019 at 07:32 AM.

  6. #6
    Registered User
    Join Date
    07-22-2019
    Location
    Israel
    MS-Off Ver
    Office 2016
    Posts
    22

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    Thanks for this solution as well. As for now, due to technical issues, I prefer using a regular Excel/VBA solution without dealing with power query.

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,156

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    Please Login or Register  to view this content.
    EDIT...
    Or perhaps....Seen as it is specific to column name....
    Please Login or Register  to view this content.
    Last edited by sintek; 07-23-2019 at 07:46 AM.

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    What technical issues do you have with Power Query? Or is it just that you're unfamiliar with it?

    Either way, it will be easier to make reference to your columns, if you format your data as a table. Then you could use something like:

    Please Login or Register  to view this content.
    This lets you specify which column headers you wish to deal with.

  9. #9
    Registered User
    Join Date
    07-22-2019
    Location
    Israel
    MS-Off Ver
    Office 2016
    Posts
    22

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    Thanks Olly. Regarding using Power Query (which indeed I'm not much familiar with...), you said that for that I have to convert my source data to a table.
    It's really seems to be a good solution, but my final product must be in a regular table format.
    So I think it is better to find other solution instead of converting twice...

  10. #10
    Registered User
    Join Date
    07-22-2019
    Location
    Israel
    MS-Off Ver
    Office 2016
    Posts
    22

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    sintek, Thanks a lot! Your second solution works perfect for me. Really appreciate your help

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    I'm intrigued about your reluctance to use Tables.

    It's not just you, there's lots of users around who are still reluctant to format data in Tables, and to use some of the newer, more powerful Excel techniques.

    I don't really understand why?

  12. #12
    Registered User
    Join Date
    07-22-2019
    Location
    Israel
    MS-Off Ver
    Office 2016
    Posts
    22

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    I prefer using tables, but for this specific need, that I must have regular table to upload to some system that doesn't recognize the table format and reject it...

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,156

    Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

    sintek, Thanks a lot! Your second solution works perfect for me.
    Pleasure...Thanks for rep +

+ 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. 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
  2. Skip x cells and fill (Fill/create weekly average from 7 days and fill down)
    By tunafishes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2016, 03:43 AM
  3. Match Data and Auto Fill Multiple Cells
    By Excel_Formula in forum Excel General
    Replies: 1
    Last Post: 06-01-2015, 03:53 PM
  4. Dynamic Table Auto Fill Help "Inconsistent Calculated Column Formula"
    By GMAGANA in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-02-2012, 12:03 PM
  5. Replies: 1
    Last Post: 09-07-2011, 05:57 PM
  6. 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
  7. auto fill data into a cell from a lookup table
    By Tetradpoint in forum Excel General
    Replies: 1
    Last Post: 04-19-2005, 12:06 PM

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