VBA combine advanced filter and dynamic range

Joined
May 19, 2019
Messages
1
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.



Month1
RankIDxxxxxxxxxx

<tbody>
</tbody>
^sheet1

xxMonthIDxxxxxxxxxx
1/1/2019
1/2/2019
1/3/2019
etc

<tbody>
</tbody>
^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>
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
880
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,708
Messages
5,446,047
Members
405,378
Latest member
pvergili

This Week's Hot Topics

Top