Results 1 to 2 of 2

Thread: VBA combine advanced filter and dynamic range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb VBA combine advanced filter and dynamic range

    I have one sheet of raw data containing 12 month with few hundreds of ID for each month. The no. of rows for each month varies. Now, I want to copy the respective month data to sheet1. By changing the number of 1/2/3 (representing the month), the data for each month can auto displayed.



    Month 1
    Rank ID xx xx xx xx xx
    ^sheet1

    xx Month ID xx xx xx xx xx
    1/1/2019
    1/2/2019
    1/3/2019
    etc
    ^raw data

    By changing the number(in red) in sheet1, the table below will auto display the relevant month data.

    I have figured out how to copy paste the dynamic table range through VBA. But it cannot be changed auto with key in the no. of month. I have to change the month in raw data sheet manually. Below is what I have tried so far.

    Sub Autofill()
    Dim rawdata As Worksheet
    Dim sheet1 As Worksheet


    Dim rowStartRawdata As Integer
    Dim rowStartsheet1 As Integer
    Dim rowEndRawdata As Long


    Set rawdata = ThisWorbook.Sheets("raw data")
    Set sheet1 = ThisWorkbook.Sheets("sheet1")


    rowStartRawdata = 2
    rowStartsheet1 = 8
    rowEndRawdata = rawdata.Cells(Rows.Count, "A").End(xlUp).Row


    With rawdata
    .Range(.Cells(rowStartRawdata, 3), .Cells(rowEndRawdata, 5)).copy (sheet1.Cells(rowStartsheet1,2))
    End With
    End Sub


    ****** id="cke_pastebin" style="position: absolute; top: 72px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">I have figured out how to copy paste the dynamic table range through VBA. But it cannot be changed auto with key in the no. of month. I have to change the month in raw data sheet manually. Below is what I have tried so far.

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    643
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA combine advanced filter and dynamic range

    Use the Worksheet_Change event of Sheet1 with the target being the cell the month number is entered into to filter the Raw Data table.
    Use the macro recorder while manually filtering the table to see how vba goes about it.
    Alter the recorded macro to use a variable (=target.value) for the month number.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •