VBA macros to auto filter copy and past the data by column

**hecgroups** · 08-01-2019, 02:43 AM

Hello again,

I am looking for a V.BA Macros to auto Filter data in column A and past in worksheet("datasheet") by column.

copy of the sample sheathes attached for your refference

**Marc L** · 08-01-2019, 03:56 AM

As a beginner starter paste this demonstration to the Dashboard worksheet module :

PHP Code:


Sub Demo1()

     Dim C%, Rg As Range, H%, Rk As Range, R&

         C = 1

         Me.UsedRange.Clear

         Application.ScreenUpdating = False

    With Sheets("ST")

        .[A1].AutoFilter

         Set Rg = .[A1].CurrentRegion

         H = Rg.Columns.Count + 1

         Set Rk = .Cells(H + 2)

    End With

        Rg.Columns(1).AdvancedFilter xlFilterCopy, , Rk, True

    For R = 2 To Rk.CurrentRegion.Rows.Count - 1

        Rg.AdvancedFilter xlFilterCopy, Rk.Resize(2), Cells(C)

        Rk(R + 1).Copy Rk(2)

        C = C + H

    Next

        Rk.CurrentRegion.Clear

        Set Rg = Nothing:   Set Rk = Nothing

        Application.ScreenUpdating = True

End Sub

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

**hecgroups** · 08-01-2019, 04:10 AM

Thanks marcl,

1 last requirement. Can v copy only first 10 rows if the data is more than 10 rows.

Thanks for ur help

**Sintek** · 08-01-2019, 04:50 AM

Option Explicit

Sub Extract()
Dim val, col As Long, sr As Long, rng As Range: col = 1
With CreateObject("System.Collections.ArrayList")
    For Each val In Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row - 1).Value
        If Not .contains(val) Then
            .Add val
            With Cells(1).CurrentRegion
                .AutoFilter 1, val
                sr = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).Row
                Set rng = Union(Range("A1:E1"), Range("A" & sr).Resize(10, 5).SpecialCells(12))
                rng.Copy Sheet1.Cells(1, col)
                col = col + 6
            End With
        End If
    Next val
End With
Sheet1.UsedRange.Columns.AutoFit
Sheet26.AutoFilterMode = False
End Sub

**hecgroups** · 08-01-2019, 05:34 AM

Thanks both of u it us working as i expected.
Topic marked as closed and reputation added to both of u.

**Sintek** · 08-01-2019, 05:37 AM

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks.gif

VBA macros to auto filter copy and past the data by column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA macros to auto filter copy and past the data by column

Hi ! Try this ‼

Re: VBA macros to auto filter copy and past the data by column

Re: VBA macros to auto filter copy and past the data by column

Re: VBA macros to auto filter copy and past the data by column

Re: VBA macros to auto filter copy and past the data by column

Thread Information

Users Browsing this Thread

Similar Threads

VBA Code to Copy Data From Last Row Based on A Value and Past into Blank Column

Copy value of one cell and past in new column only where data exists

Apply auto filter and copy data one below other

[SOLVED] Copy data from fillable user form to database tab in column format/recall past records

Macro to Set Filter and Copy past

Copy/paste past protection and macros

auto copy and past

Bookmarks

Bookmarks

Posting Permissions