John Price
New Member
I have a macro that copies a column from a table and pastes it into another new sheet that has been created. This table has 460 rows of data (not always but for this example, yes) When I copy the column data over to the new sheet sometimes it will copy all 460 rows and sometimes it will only copy the first 42 rows. Nothing changes in the table. I noticed if I step into the code 90% of the time it will copy all 460 rows. If I run it (without stepping into it) 90% of the time it will copy only 42 rows of data from the table. I can't post the code here as it has confidential stuff in it. But I think I narrowed down the issue to a single line of code and wanted to see if anyone else here might know why it is doing this and can provide a solid solution.
My_Range.Columns(FieldNum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet1").Range("A3"), Unique:=True
My_Range is a declared as a Range where it sets the Range of the Table by using a function called LastRow where it uses cell A2 the first header of the Table and then finds the LastRow of the sheet. Set My_Range = ws4.Range("A2:O" & LastRow(ActiveSheet))
I have not found there to be any errors when at this stage so I have ruled this out already.
FieldNum is a string set as 2 which selects the second column in the table. Just to reiterate this is the line that seems to not be working correctly.
My_Range.Columns(FieldNum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet1").Range("A3"), Unique:=True
I know I could probably use a better way of declaring the table range using the table itself but I wrote this code a long time ago and instead of making a bunch of changes I'd rather make a simple modification if its possible.
My_Range.Columns(FieldNum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet1").Range("A3"), Unique:=True
My_Range is a declared as a Range where it sets the Range of the Table by using a function called LastRow where it uses cell A2 the first header of the Table and then finds the LastRow of the sheet. Set My_Range = ws4.Range("A2:O" & LastRow(ActiveSheet))
I have not found there to be any errors when at this stage so I have ruled this out already.
FieldNum is a string set as 2 which selects the second column in the table. Just to reiterate this is the line that seems to not be working correctly.
My_Range.Columns(FieldNum).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet1").Range("A3"), Unique:=True
I know I could probably use a better way of declaring the table range using the table itself but I wrote this code a long time ago and instead of making a bunch of changes I'd rather make a simple modification if its possible.