+ Reply to Thread
Results 1 to 5 of 5

How to copy filtered data from one table to another table's last row?

  1. #1
    Registered User
    Join Date
    09-14-2019
    Location
    Manila, Philippines
    MS-Off Ver
    O365
    Posts
    5

    How to copy filtered data from one table to another table's last row?

    Hi Folks,

    Although I have been working with basic VBA for some time now, I still consider myself as new to VBA. As such, I would like to ask for some assistance on the current challenge that I am trying to solve:

    I have 2 tables, source and target. In source table, I will filter one of the columns, and would like to copy those filtered data into the target table's last row (and expand if more than 1 row of data is being copied).

    I have been testing this code, but unfortunately, I can't find how to do the final step of copying the filtered data:

    Please Login or Register  to view this content.
    Hoping someone can help on this. TIA!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this ‼


    A starter demonstration to paste to the Sheet3 (Sheet2) worksheet module :

    PHP Code: 
    Sub Demo1()
      Const 
    "target"
        
    Dim R&, C&
            
    Application.ScreenUpdating False
        With Me
    .ListObjects("source")
            .
    Range.AutoFilter
            
    .Range.AutoFilter 2"a"
            
    = [SUBTOTAL(103,source[Col1])]
         If 
    R Then
            C 
    Me.ListObjects(T).ListRows.Count
            R 
    + (0)
            If 
    R Then .HeaderRowRange.EntireRow.Resize(R).Insert
            
    .DataBodyRange.Copy Me.ListObjects(T).Range(C1)
         
    End If
            .
    Range.AutoFilter 2
        End With
            Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 09-15-2019 at 06:59 AM. Reason: optimization …

  3. #3
    Registered User
    Join Date
    09-14-2019
    Location
    Manila, Philippines
    MS-Off Ver
    O365
    Posts
    5

    Re: Hi ! Try this ‼

    Thanks Marc for this code. I tried to run it as is, but it was giving me the error "Invalid use of Me keyword".

    I added a worksheet object instead and replace the Me keyword and it worked!

    I just have to study the lines of your code to understand what these does

    Thanks!

  4. #4
    Registered User
    Join Date
    09-14-2019
    Location
    Manila, Philippines
    MS-Off Ver
    O365
    Posts
    5

    Re: Hi ! Try this ‼

    Sorry Marc, can you help me to understand these 2 lines and what are they supposed to do? I can't seem to comprehend these:

    Please Login or Register  to view this content.
    My understanding here is that you are calculating the number of cells that are not blank, is that correct?

    and

    Please Login or Register  to view this content.
    Why does the C needs to be equated to zero, not just add zero instead?

    Thanks again for the help!
    Last edited by roose; 09-15-2019 at 11:37 PM.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Thanks for the rep' added !


    Quote Originally Posted by roose View Post
    error "Invalid use of Me keyword".
    Your bad : no issue if you have followed the dark red direction just before the code !
    But well done as you understand what Me refers to …


    Quote Originally Posted by roose View Post
    you are calculating the number of cells that are not blank, is that correct?
    Almost yes : as you can check on Excel side with its help of the SUBTOTAL worksheet function
    this worksheet formula counts the visible cells not blank …


    Quote Originally Posted by roose View Post
    Why does the C needs to be equated to zero, not just add zero instead?
    No, C does not need to be equal to zero, it's just a condition like If C = 0 Then R = R - 1
    as according to your attachment no need to insert a row if the table has no row.
    Without this codeline, instead of keeping 2 blank rows between the tables
    another blank row is added according to your attachment …

+ 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] Requesting critique on macro which copy filtered data to new table. = ) Workbook inside.
    By Elijah in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-18-2019, 04:38 PM
  2. Filtered data showing when pivot table copy and pasted over
    By Simone Fick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2017, 07:30 AM
  3. Copy Filtered data from Pivot Table to another Worksheet
    By Duey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2017, 08:12 PM
  4. [SOLVED] Vba to copy Filtered rows from table to another table
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2016, 06:46 AM
  5. VBA to copy filtered data from excel sheet to access table
    By sugat11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2016, 02:59 AM
  6. Replies: 3
    Last Post: 11-04-2013, 07:50 PM
  7. Replies: 0
    Last Post: 02-20-2012, 02:53 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1