loop trough filter list

[Sjon1974]

All,

I have problem. I making a program that when a new period is imported then it needs to create subtotals and copy that in a specific field.
But i have per department a different worksheet (layout is all the same) Instead of copying everything 20000 times per period and department. I want to loop trough the list of departments.

This is what i have now, but now i am calling everytime to the new department. 13 department and 24 periods......

Sub BPO1()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim LASTROW As Long
    Dim Cl As Range
    Set ws1 = ThisWorkbook.Sheets("Database")
    Set ws2 = ThisWorkbook.Sheets("ACUM BPO")
    ws1.Range("G1").AutoFilter 7, "1"
    ws1.Range("G1").AutoFilter 203, "ACUM BPO"
    ws2.Range("C6") = "=SUBTOTAL(9,sueldo)"
    ws2.Range("C15") = "=SUBTOTAL(9,SUBSIDIO)"
    ws2.Range("C21") = "=SUBTOTAL(9,PRIMA_VACACIONAL)"
    ws2.Range("C22") = "=SUBTOTAL(9,VACACIONES)"
    ws2.Range("C32") = "=SUBTOTAL(9,ISR_A_CARGO)"
    ws2.Range("C57") = "=SUBTOTAL(9,CREDITO_INFONAVIT)"
    ws2.Range("C58") = "=SUBTOTAL(9,CREDITO_FONACOT)"
    ws2.Range("C59") = "=SUBTOTAL(9,IMSS)"
    'copy
    ws2.Range("C6:C28").Copy
    ws2.Range("C6:C28").PasteSpecial Paste:=xlPasteValues
    ws2.Range("C32:C60").Copy
    ws2.Range("C32:C60").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Call IPSAM1
    End Sub

problem is that the ws2 is changing the whole time to the new worksheet of the departement.

 

[DanteAmor]

Run the macro again, when you receive the error press the debug button.
A line in the macro will be highlighted. Tell me what that line is.
Put the mouse over the variable "ky", the content of the variable should appear. Tell me what the content of that variable says.

How does the script knows the name of the different worksheets?

The macro reads the data in column 203, each of that data represents a department. In post #2 I asked you if all the departments have their sheet and you answered: yes.

 

[Sjon1974]

Here it is going wrong

 

[Sjon1974]

Apperently i cannot edit my last post.

I found the problem with the Immediate window, It stopted with the projectos with BPO. Turned out ACUM INVOLVE had a extra space after the name of the tab and not in the projects name in column 203!

Thank you so much for this amazing work!!

 

[DanteAmor]

I'm glad to hear that it already works for you.