Na chwile obecną zrobiłem sama aktualizację (czyli kasuje dane z arkuszy innych niz ogólne i tworzy podział od początku) Jest też zaberzpieczenie gdybyś się pomylił w kodzie umowy i nie było by takiego arkusza wtedy taka umowa trafia do arkusza "inne" Oczywiście aby działało musisz pozwolić na włączenie makr. wstawianie ostatniego wiersza później bo teraz nie mama czasu Sub aktualizuj() Dim ostW As Integer, i As Integer, wiersz As Integer Dim kod As String, arkusz As String Dim nazwa As Worksheet Dim test As Boolean Sheets("Umowy EG").Cells.Delete Shift:=xlUp Sheets("Umowy EI").Cells.Delete Shift:=xlUp Sheets("Umowy DZ").Cells.Delete Shift:=xlUp Sheets("Umowy EZ").Cells.Delete Shift:=xlUp Sheets("Inne").Cells.Delete Shift:=xlUp ostW = Sheets("Ogólne").Range("B65535").End(xlUp).Row For i = 2 To ostW kod = UCase(Left(Sheets("Ogólne").Cells(i, 2), 1) & Right(Left(Sheets("Ogólne").Cells(i, 2), 3), 1)) arkusz = "Umowy " & kod test = True For Each nazwa In Worksheets If arkusz = nazwa.Name Then test = False Exit For End If Next nazwa If test Then arkusz = "Inne" wiersz = WorksheetFunction.Max(Sheets(arkusz).Columns(1)) + 1 If wiersz = 1 Then Sheets("Ogólne").Rows(1).Copy (Sheets(arkusz).Rows(1)) End If Sheets("Ogólne").Rows(i).Copy (Sheets(arkusz).Rows(wiersz + 1)) Sheets(arkusz).Cells(wiersz + 1, 1) = wiersz Next i Sheets("Umowy EG").Cells.EntireColumn.AutoFit Sheets("Umowy EI").Cells.EntireColumn.AutoFit Sheets("Umowy DZ").Cells.EntireColumn.AutoFit Sheets("Umowy EZ").Cells.EntireColumn.AutoFit Sheets("Inne").Cells.EntireColumn.AutoFit End Sub edit: dodałem "wprowadz" wprowadza ostatni wiersz do arkusza ale nie sprawdza czy już taki był więc można wprowadzić parę razy ostatni wiersz do bazy wtedy najlepiej aktualizuj i wszystko sie uporządkuje :) tu kod wprowadz Sub wprowadz() Dim ostW As Integer, lp As Integer, wiersz As Integer Dim kod As String, arkusz As String Dim nazwa As Worksheet Dim test As Boolean ostW = Sheets("Ogólne").Range("B65535").End(xlUp).Row kod = UCase(Left(Sheets("Ogólne").Cells(ostW, 2), 1) & Right(Left(Sheets("Ogólne").Cells(ostW, 2), 3), 1)) arkusz = "Umowy " & kod test = True For Each nazwa In Worksheets If arkusz = nazwa.Name Then test = False Exit For End If Next nazwa If test Then arkusz = "Inne" wiersz = Sheets(arkusz).Range("B65535").End(xlUp).Row lp = WorksheetFunction.Max(Sheets(arkusz).Columns(1)) + 1 Sheets("Ogólne").Rows(ostW).Copy (Sheets(arkusz).Rows(wiersz + 1)) Sheets(arkusz).Cells(wiersz + 1, 1) = lp MsgBox "Wprowadzono umowę", vbInformation, "Info" End Sub
" Lp. " , " Numer Umowy " , " Kontrahent " , " Nazwa " , " Adres zameldowania " , " Adres Korespondencyjny " , " Data rozpocz?cia " , " Data zako?czenia " , " Taryfa " , " Liczba Uk?adów Pom. " , " Moc Umowna " , " Symbol Okresu R. " , " Przy??cze " , " Typ licznika " , " Numer licznika " , " Przek?. pr?dowa " , " Przek?. napi?ciowa " , " Taryfa " , " Typ taryfy "
" 1. " , " E/G/001/2000 " , " K001 " , " Kozie? Marian " ,,, " 01-01-04 " ,, " G12kPR " , " 1 " , " 10 " , " D " , " 2.3.2. " , " 4C52cd " , " 10450236 " , " 1/1 " , " 1/1 " , " G12kPR " , " G "
" 3. " , " E/G/003/2000 " , " B003 " , " Berg Arkadiusz " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 4.1.1. " , " C52d " , " 11132996 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 4. " , " E/G/004/2000 " , " W004 " , " W?odarczyk Bogus?aw " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 11.5 " , " D " , " 3.8.3. " , " C52d " , " 11120828 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 25. " , " D/Z/1/2012 " , " E321 " , " Energomonta? " ,,, " 09-01-12 " ,, " C21z " , " 1 " , " 140 " , " M " , " ST-11 sek. 1 pole 1.15 " , " EABM " , " 8250000426 " , " 150/5 " , " 1/1 " , " C21z " , " C "
" 9. " , " E/G/010/2002 " , " W010 " , " Wengrzy?ski Ryszard " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 24 " , " D " , " 3.3.2. " , " C52d " , " 11131576 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 10. " , " E/G/011/2000 " , " G011 " , " Gregorczyk Mieczys?aw " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 2.3.2. " , " C52d " , " 11130774 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 11. " , " E/G/012/2000 " , " P012 " , " Podsiad?y Pawe? " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 4 " , " D " , " 3.7.3. " , " 4C52d " , " 11120824 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 12. " , " E/G/013/2000 " , " P013 " , " Paszkowski Jan " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 3.5.4. " , " C52d " , " 11132998 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 13. " , " E/G/014/2001 " , " I014 " , " Juszczak Bogus?aw " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 10 " , " D " , " 1.2.1. " , " C52d " , " 11336961 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 18. " , " E/I/086/2000 " , " B086 " , " B?aszczyk Kazimierz " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 6 " , " D " , " 3.8.1. " , " 4C52d " , " 11535333 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 19. " , " s/I/090/2000 " , " D090 " , " Dziubo? Marek " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 4.5 " , " D " , " 2.3.4. " , " 4C52d " , " 11536273 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 20. " ,, " W101 " , " Wojtasi?ski Tomasz " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 3.5.2. " , " 4C52d " , " 11951035 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 21. " , " E/I/120/2003 " , " O020 " , " Osi?ski Wojciech " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 12 " , " D " , " 3.8.1. " , " 6C8d " , " 12380009 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 22. " , " E/I/127/2003 " , " B027 " , " Bary?a Jacek " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 12 " , " D " , " 1.1.2. " , " 6C8d " , " 12470703 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 23. " , " E/I/161/2005 " , " K061 " , " Ko?ba Piotr " ,,, " 01-24-05 " ,, " C11ik " , " 1 " , " 3 " , " D " , " 2.6.1. " , " 6C8d " , " 12981088 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 24. " , " E/I/174/2005 " , " K043 " , " Ka?mierczak S?awomir " ,,, " 05-11-05 " ,, " C11ik " , " 1 " , " 9 " , " D " , " 6.5.4. " , " 6C8d " , " 13062405 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 2. " , " E/G/002/2000 " , " I002 " , " Jab?o?ska-Bielecka Ma?gorzata " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 3.2.3. " , " C52d " , " 11133000 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 26. " , " D/Z/10/2012 " , " B60 " , " Bin? " ,,, " 09-01-12 " ,, " C21z " , " 1 " , " 50 " , " M " , " ST-15 sek. 2 pole 8.13 " , " EABM " , " 8250001066 " , " 100/5 " , " 1/1 " , " C21z " , " C "
" 27. " , " E/Z/618//12011 " , " s302 " , " SKB Sp. z o.o. Spó?ka Komandytowa " ,,, " 08-04-11 " ,, " C21z " , " 1 " , " 120 " , " M " , " ST/24 s. I p. 5.8. " , " EABM " , " 8250000435 " , " 200/5 " , " 1/1 " , " C21z " , " C "
" 28. " , " E/Z/625/1/2012 " , " P232 " , " PSE Sp. z o.o. " ,,, " 07-01-12 " ,, " C11z " , " 1 " , " 20 " , " M " , " budynek BTS na zapleczu Instal " , " sEA " , " 5030006459 " , " 75/5 " , " 1/1 " , " C11z " , " C "
" 29. " , " E/Z/819/2012 " , " B147 " , " Babcock Borsig Steinmuller " ,,, " 01-09-12 " ,, " C21z " , " 1 " , " 100 " , " M " , " ST-25 sek.2 p.4.1 " , " sEA " , " 503-0006494 " , " 300/5 " , " 1/1 " , " C21z " , " C "
" 5. " , " E/G/005/2000 " , " S005 " , " Szulc Roman " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 10 " , " D " , " 4.1.2. " , " C52d " , " 11120823 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 14. " , " E/I/006/2000 " , " S006 " , " Rombud-Handel Szulc Roman " ,,, " 01-01-04 " ,, " C11k " , " 1 " , " 12 " , " D " , " 4.1.2. " , " C52d " , " 11120829 " , " 1/1 " , " 1/1 " , " C11k " , " C "
" 15. " , " E/I/023/2000 " , " T023 " , " Teslar Robert " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 3.4.1. " , " 4C52d " , " 11319035 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 16. " , " E/I/083/2000 " , " L083 " , " Lasota Zbigniew " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 1.1.5. " , " 4C52d " , " 11535315 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 17. " , " E/I/085/2000 " , " K085 " , " Komorowski Andrzej " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 1.8.3. " , " 4C52d " , " 11536252 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" Lp. " , " Numer Umowy " , " Kontrahent " , " Nazwa " , " Adres zameldowania " , " Adres Korespondencyjny " , " Data rozpocz?cia " , " Data zako?czenia " , " Taryfa " , " Liczba Uk?adów Pom. " , " Moc Umowna " , " Symbol Okresu R. " , " Przy??cze " , " Typ licznika " , " Numer licznika " , " Przek?. pr?dowa " , " Przek?. napi?ciowa " , " Taryfa " , " Typ taryfy "
" 1 " , " E/G/001/2000 " , " K001 " , " Kozie? Marian " ,,, " 01-01-04 " ,, " G12kPR " , " 1 " , " 10 " , " D " , " 2.3.2. " , " 4C52cd " , " 10450236 " , " 1/1 " , " 1/1 " , " G12kPR " , " G "
" 2 " , " E/G/003/2000 " , " B003 " , " Berg Arkadiusz " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 4.1.1. " , " C52d " , " 11132996 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 3 " , " E/G/004/2000 " , " W004 " , " W?odarczyk Bogus?aw " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 11.5 " , " D " , " 3.8.3. " , " C52d " , " 11120828 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 4 " , " E/G/010/2002 " , " W010 " , " Wengrzy?ski Ryszard " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 24 " , " D " , " 3.3.2. " , " C52d " , " 11131576 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 5 " , " E/G/011/2000 " , " G011 " , " Gregorczyk Mieczys?aw " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 2.3.2. " , " C52d " , " 11130774 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 6 " , " E/G/012/2000 " , " P012 " , " Podsiad?y Pawe? " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 4 " , " D " , " 3.7.3. " , " 4C52d " , " 11120824 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 7 " , " E/G/013/2000 " , " P013 " , " Paszkowski Jan " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 3.5.4. " , " C52d " , " 11132998 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 8 " , " E/G/014/2001 " , " I014 " , " Juszczak Bogus?aw " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 10 " , " D " , " 1.2.1. " , " C52d " , " 11336961 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 9 " , " E/G/002/2000 " , " I002 " , " Jab?o?ska-Bielecka Ma?gorzata " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 7.5 " , " D " , " 3.2.3. " , " C52d " , " 11133000 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" 10 " , " E/G/005/2000 " , " S005 " , " Szulc Roman " ,,, " 01-01-04 " ,, " G11k3 " , " 1 " , " 10 " , " D " , " 4.1.2. " , " C52d " , " 11120823 " , " 1/1 " , " 1/1 " , " G11k3 " , " G "
" Lp. " , " Numer Umowy " , " Kontrahent " , " Nazwa " , " Adres zameldowania " , " Adres Korespondencyjny " , " Data rozpocz?cia " , " Data zako?czenia " , " Taryfa " , " Liczba Uk?adów Pom. " , " Moc Umowna " , " Symbol Okresu R. " , " Przy??cze " , " Typ licznika " , " Numer licznika " , " Przek?. pr?dowa " , " Przek?. napi?ciowa " , " Taryfa " , " Typ taryfy "
" 1 " , " E/I/086/2000 " , " B086 " , " B?aszczyk Kazimierz " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 6 " , " D " , " 3.8.1. " , " 4C52d " , " 11535333 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 2 " , " E/I/120/2003 " , " O020 " , " Osi?ski Wojciech " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 12 " , " D " , " 3.8.1. " , " 6C8d " , " 12380009 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 3 " , " E/I/127/2003 " , " B027 " , " Bary?a Jacek " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 12 " , " D " , " 1.1.2. " , " 6C8d " , " 12470703 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 4 " , " E/I/161/2005 " , " K061 " , " Ko?ba Piotr " ,,, " 01-24-05 " ,, " C11ik " , " 1 " , " 3 " , " D " , " 2.6.1. " , " 6C8d " , " 12981088 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 5 " , " E/I/174/2005 " , " K043 " , " Ka?mierczak S?awomir " ,,, " 05-11-05 " ,, " C11ik " , " 1 " , " 9 " , " D " , " 6.5.4. " , " 6C8d " , " 13062405 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 6 " , " E/I/006/2000 " , " S006 " , " Rombud-Handel Szulc Roman " ,,, " 01-01-04 " ,, " C11k " , " 1 " , " 12 " , " D " , " 4.1.2. " , " C52d " , " 11120829 " , " 1/1 " , " 1/1 " , " C11k " , " C "
" 7 " , " E/I/023/2000 " , " T023 " , " Teslar Robert " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 3.4.1. " , " 4C52d " , " 11319035 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 8 " , " E/I/083/2000 " , " L083 " , " Lasota Zbigniew " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 1.1.5. " , " 4C52d " , " 11535315 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 9 " , " E/I/085/2000 " , " K085 " , " Komorowski Andrzej " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 1.8.3. " , " 4C52d " , " 11536252 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 10 " , " E/I/085/2000 " , " K085 " , " Komorowski Andrzej " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 1.8.3. " , " 4C52d " , " 11536252 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" Lp. " , " Numer Umowy " , " Kontrahent " , " Nazwa " , " Adres zameldowania " , " Adres Korespondencyjny " , " Data rozpocz?cia " , " Data zako?czenia " , " Taryfa " , " Liczba Uk?adów Pom. " , " Moc Umowna " , " Symbol Okresu R. " , " Przy??cze " , " Typ licznika " , " Numer licznika " , " Przek?. pr?dowa " , " Przek?. napi?ciowa " , " Taryfa " , " Typ taryfy "
" 1 " , " D/Z/1/2012 " , " E321 " , " Energomonta? " ,,, " 09-01-12 " ,, " C21z " , " 1 " , " 140 " , " M " , " ST-11 sek. 1 pole 1.15 " , " EABM " , " 8250000426 " , " 150/5 " , " 1/1 " , " C21z " , " C "
" 2 " , " D/Z/10/2012 " , " B60 " , " Bin? " ,,, " 09-01-12 " ,, " C21z " , " 1 " , " 50 " , " M " , " ST-15 sek. 2 pole 8.13 " , " EABM " , " 8250001066 " , " 100/5 " , " 1/1 " , " C21z " , " C "
" Lp. " , " Numer Umowy " , " Kontrahent " , " Nazwa " , " Adres zameldowania " , " Adres Korespondencyjny " , " Data rozpocz?cia " , " Data zako?czenia " , " Taryfa " , " Liczba Uk?adów Pom. " , " Moc Umowna " , " Symbol Okresu R. " , " Przy??cze " , " Typ licznika " , " Numer licznika " , " Przek?. pr?dowa " , " Przek?. napi?ciowa " , " Taryfa " , " Typ taryfy "
" 1 " , " E/Z/618//12011 " , " s302 " , " SKB Sp. z o.o. Spó?ka Komandytowa " ,,, " 08-04-11 " ,, " C21z " , " 1 " , " 120 " , " M " , " ST/24 s. I p. 5.8. " , " EABM " , " 8250000435 " , " 200/5 " , " 1/1 " , " C21z " , " C "
" 2 " , " E/Z/625/1/2012 " , " P232 " , " PSE Sp. z o.o. " ,,, " 07-01-12 " ,, " C11z " , " 1 " , " 20 " , " M " , " budynek BTS na zapleczu Instal " , " sEA " , " 5030006459 " , " 75/5 " , " 1/1 " , " C11z " , " C "
" 3 " , " E/Z/819/2012 " , " B147 " , " Babcock Borsig Steinmuller " ,,, " 01-09-12 " ,, " C21z " , " 1 " , " 100 " , " M " , " ST-25 sek.2 p.4.1 " , " sEA " , " 503-0006494 " , " 300/5 " , " 1/1 " , " C21z " , " C "
" Lp. " , " Numer Umowy " , " Kontrahent " , " Nazwa " , " Adres zameldowania " , " Adres Korespondencyjny " , " Data rozpocz?cia " , " Data zako?czenia " , " Taryfa " , " Liczba Uk?adów Pom. " , " Moc Umowna " , " Symbol Okresu R. " , " Przy??cze " , " Typ licznika " , " Numer licznika " , " Przek?. pr?dowa " , " Przek?. napi?ciowa " , " Taryfa " , " Typ taryfy "
" 1 " , " s/I/090/2000 " , " D090 " , " Dziubo? Marek " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 4.5 " , " D " , " 2.3.4. " , " 4C52d " , " 11536273 " , " 1/1 " , " 1/1 " , " C11ik " , " C "
" 2 " ,, " W101 " , " Wojtasi?ski Tomasz " ,,, " 01-01-04 " ,, " C11ik " , " 1 " , " 10 " , " D " , " 3.5.2. " , " 4C52d " , " 11951035 " , " 1/1 " , " 1/1 " , " C11ik " , " C "