Sjon1974
New Member
- Joined
- Apr 1, 2020
- Messages
- 38
- Office Version
- 365
- Platform
- Windows
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......
problem is that the ws2 is changing the whole time to the new worksheet of the departement.
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......
VBA Code:
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.