VBA combine advanced filter and dynamic range

[ihavesomanyquestions]

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.



[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rank[/TD]
[TD]ID[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
^sheet1

[TABLE="width: 500"]
<tbody>[TR]
[TD]xx[/TD]
[TD]Month[/TD]
[TD]ID[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/1/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/2/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/3/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
^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.</body>

 

[NoSparks]

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.