Table Range and Dynamic Range

[Aleciafxc]

I have a table A:AE with expanding data. I want to filter this data and select only 3 of the columns to copy, L-U-Y. After I filter I then set the range to copy I can't seem to set that range to always match the current Table range. I have searched this forum and others. I am either not searching the correct terms or I am so much a novice I just do not understand. I am truly at a loss and do need help. Here is my code that works but does not expand the copy range with the table range. In short as soon as my table2 extends past 626 I have to alter the macro. If i set my Table2 to 2001 rows then my workbook doubles in space due to the amount of formulas. I am Desperate.

Sub DirllDownCopyPP()
If range("L16") < 1 Then Exit Sub
Application.ScreenUpdating = False

Sheets("PP").Select
ActiveSheet.ListObjects("Table2").range.AutoFilter Field:=1, Criteria1:= _
Sheets("DrillDown").range("a15")
range("L2:L626,U2:U626,Y2:Y626").Select
Selection.Copy
Sheets("DrillDown").Select
range("a18").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DrillDown").Select
range("a15").Select

End Sub

 

[Jenawade]

http://www.mrexcel.com/forum/showthread.php?t=284211 should hopefully help you with this.

 

[Aleciafxc]

This looks great but i can not get it to work. I must be referencing it incorrectly. Any assistance would be appreciated.

Sub DirllDownCopyPP()
If range("L16") < 1 Then Exit Sub
Application.ScreenUpdating = False

Sheets("PP").Select
ActiveSheet.ListObjects("Table2").range.AutoFilter Field:=1, Criteria1:= _
Sheets("DrillDown").range("a15")

range("L2:L" & NoOfRows & "U2:U" & NoOfRows & "Y2:Y" & NoOfRows).Select

Selection.Copy
Sheets("DrillDown").Select
range("a18").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DrillDown").Select
range("a15").Select

End Sub

 

[Jenawade]

I'm a VBA dummy myself, but I know you need to have this line....

NoOfRows = Sheets("Sheet1").UsedRange.Rows.Count

....using your sheet name in place of "Sheet 1" but I don't see it in your code.

Sub DirllDownCopyPP()
NoOfRows = Sheets("Sheet1").UsedRange.Rows.Count
If range("L16") < 1 Then Exit Sub
Application.ScreenUpdating = False

 

[Aleciafxc]

Thanks, but it still is copying the entire column of L. Any other ideas.

Sub DirllDownCopyPP()
noOfRows = Sheets("PP").UsedRange.Rows.Count
If range("L16") < 1 Then Exit Sub
Application.ScreenUpdating = False

Sheets("PP").Select
ActiveSheet.ListObjects("Table2").range.AutoFilter Field:=1, Criteria1:= _
Sheets("DrillDown").range("a15")

ActiveSheet.range("L2:L" & noOfRows).Copy

Sheets("DrillDown").Select
range("a18").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DrillDown").Select
range("a15").Select

End Sub