Table Range and Dynamic Range

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77
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.:rolleyes::rolleyes::rolleyes:

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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77
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

Board Regular
Joined
Apr 8, 2002
Messages
231
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

Board Regular
Joined
Mar 21, 2009
Messages
77
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
 

Forum statistics

Threads
1,140,920
Messages
5,703,173
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top