Compacting a routine in vba

[rfletcher35]

Hi guys I have the following routine

ActiveSheet.Range("$A$3:$J$10000").AutoFilter Field:=9, Criteria1:=Array("=" _
), Operator:=xlFilterValues, Criteria2:=Array(1, "1/31/2021")
Range("M1").Select
Selection.Copy
Sheets("The Omerta Group").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

This routine filters on column 9 for January, copies a subtotal figure M1, then goes to another sheet and pastes that information for me into a required cell C2.
This repeats for Feb - Dec and pastes the detail in then next cell down on the other sheet, C3, C4, C5.......

As you can imagine this routine occurs 12 time and I was wondering if someone knew of a way to condense all this?

I ask as I also need to add this routine for around 6 or 7 sheets for other company names

Thanks

Fletch

 

[bobsan42]

as far as these lines of code go you can do it like this:

ActiveSheet.Range("$A$3:$J$10000").AutoFilter Field:=9, Criteria1:=Array("=" _
), Operator:=xlFilterValues, Criteria2:=Array(1, "1/31/2021")
Sheets("The Omerta Group").Range("C2").Value = ActiveSheet.Range("M1").Value

About the repeating - you will have to provide a bit more information e.g. where do the values come from for each month.
why do you need to filter the data before transfering the value ? Is it a SUBTOTAL formula in M1?
Where is the data for the other companies?

 

[Fluff]

How about

Sub rfletcher()
   Dim i As Long
   
   For i = 1 To 12
      ActiveSheet.Range("$A$3:$J$10000").AutoFilter Field:=9, Operator:=xlFilterValues, Criteria2:=Array(1, i & "/1/2021")
      Sheets("The Omerta Group").Range("C" & i + 1).Value = Range("M1").Value
   Next i
End Sub

 

[rfletcher35]

as far as these lines of code go you can do it like this:

ActiveSheet.Range("$A$3:$J$10000").AutoFilter Field:=9, Criteria1:=Array("=" _
), Operator:=xlFilterValues, Criteria2:=Array(1, "1/31/2021")
Sheets("The Omerta Group").Range("C2").Value = ActiveSheet.Range("M1").Value

About the repeating - you will have to provide a bit more information e.g. where do the values come from for each month.
why do you need to filter the data before transfering the value ? Is it a SUBTOTAL formula in M1?
Where is the data for the other companies?

The values come from data that I import into a sheet called "Ticket Export Data" field 9 is "Completed Data"
I filter the data before transferring as it's the way I know unless you know a better way
Yes the Subtotal formula is in M1 "=SUBTOTAL(3,I4:I8000)"
Data for the other companies is in "Ticket Export Data" Column D, D4 onwards

Please let me know if there is anything else you need, thank you for your help

Fletch

 

[rfletcher35]

How about

Sub rfletcher()
   Dim i As Long
  
   For i = 1 To 12
      ActiveSheet.Range("$A$3:$J$10000").AutoFilter Field:=9, Operator:=xlFilterValues, Criteria2:=Array(1, i & "/1/2021")
      Sheets("The Omerta Group").Range("C" & i + 1).Value = Range("M1").Value
   Next i
End Sub

Hi,

Tried this but the figures were all incorrect

Fletch

 

[bobsan42]

try it like this:

Criteria2:=Array(1,format(DateSerial(2021,i+1,1)-1,"m/d/yyyy"))

 

[Fluff]

Tried this but the figures were all incorrect

If you step through the code using F8, does the sheet filter correctly?

 

[rfletcher35]

If you step through the code using F8, does the sheet filter correctly?

Yes it filters correctly it is failing when it puts the figure in "M1"
I have the formula in there of "=SUBTOTAL(3,I4:I8000)" to just calculate filtered data

 

[Fluff]

Is the value in M1 correct for the filter?

 

[rfletcher35]

Is the value in M1 correct for the filter?

No it doesn't seem to be