I am currently stumped by an issue I have encountered with a straightforward macro written in Excel VBA. I am trying to craft a solution that will take a dynamically changing range with a defined column set ($A:$N) based on a filter, copy it into another worksheet in the same workbook, and then eliminate the entry from the original sheet but only for the first 14 columns. The snag I'm hitting with the code, which may actually fall outside the scope of the VBA, is every time I move an item set from the source worksheet into the new sheet and delete its contents it applies the deletion across the entire row and removes formulas from my range. As an example, if I move two fragmented line rows (set between $A:$N where no equations exist) into the new sheet and then have these contents deleted on the original sheet it will eliminate my formula entries in columns O:S. I want to keep my formula volume stable through a specific row (say, through row 1001) while still being able to delete entries out of the columns that precede O:S. Here's the code I'm working with:
Sheets("Raw Data").Select
Rows.Hidden = False
Columns.Hidden = False
ActiveSheet.Range("$A:$S").AutoFilter Field:=1, Criteria1:="Yes"
Range("$A$2:$N$1001").Copy Destination:=Sheets("Compliant Accounts List").Range("A65536").End(xlUp).Offset(1, 0)
Sheets("Raw Data").Select
ActiveSheet.Range("$A:$S").AutoFilter Field:=1
ActiveSheet.Range("$A:$S").AutoFilter Field:=1, Criteria1:="Yes"
Range(Cells(2, 1), Cells(Rows.Count, 14).End(xlToLeft)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A:$S").AutoFilter Field:=1
Application.CutCopyMode = False
Other than some abridgements which I know about with the filtering, is there anything in here I can do to achieve my desired end result of a stable formula count in the source worksheet columns outside of the range that is being deleted? I'd greatly appreciate any insight on this query. Thanks a lot for your help.
Bookmarks