VBA code to move data with selected criteria - coding questions

[jnash]

Hi,

I am trying to create some code that will move data from one worksheet to another if it meets a certain criteria.The criteria will be a product category and a week number. If possible the week number will change incrementally and on a weekly basis. For example this week is week number 37 when I run the code next week the 37 will increase to look for week 38. (I am using excel 2013)

So far I have been using auto filters and haven't had much luck, I have also used an if and copy code (posted below)

Auto filter code:

 ActiveSheet.Range("$A$2:$X$300").AutoFilter Field:=26, Criteria1:="Bath"
    ActiveSheet.Range("$A$2:$X$300").AutoFilter Field:=2, Criteria1:=“37”
 
 
Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
 
 
    Sheets("Bath Data").Select
   Range("A1").Select
   
ActiveSheet.Paste

If and copy code:

Dim i, LastRow
 
LastRow = Sheets("Units YTD").Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
  
    If Sheets("Units YTD").Cells(i, "Z").Value = "Bath" Then ....

I also want the code to paste the new data below the previous data when it is pasted into the new sheet.

to achieve this I was using this code:

Sheets("Units YTD").Range("A" & i).Copy Destination:=Sheets("Bath Data").Range("A" & Rows.Count).End(xlUp).Offset(1)

Could the combination of the three parts of code I have pasted here be used to achieve the outcome I want ?

or is there a new code I could use ? (I am very new to VBA so I feel I could be missing something obvious)

Thanks in advance for your help, apologies if the format of my post does not follow the guidelines or it is confusing.