Data z jednoho listu do druheho

[Rent_cars]

Zdravím experty přes Excel .

Potřeboval bych Vaší radu. Přikládám zde soubor, pro lepší pochopení problému. V listu Totaly_X je přehled půjčených firemních aut. V listu Totaly_1 jsou auta, které byly půjčeny za konkrétní období. Co potřebuji:

V přehledu (Totaly_X) jsou 4 auta. Konkrétní výpůjčku identifikuji pomocí sloupce Totaly_ID. V listě Totaly_1 je ale aut 6 a já bych potřeboval, aby pomocí nějakého vzorce, formule, nebo něčeho jiného co existuje se zjistilo, které auta (podle Totaly_ID) jsou v listě Totaly_1 a nejsou v Totaly_X. Údaj, který tahám z listu Totaly_1 je used petrol [L]. V Listě Totaly_X je funkce SVYHLEDAT a pomocí té funkce natáhnu do totaly_X spotřebovaný benzín. Jenže potřebuji, aby se do Listu Totaly_X přidaly i ty auta, která jsou v totaly_1. V listě výsledek jsem udělal to, co by se mělo po spuštění vzorce stát. Jednoduše. Auta (řádky) která jsou v totaly_1 potřebuji připsat do listu Totaly_X a used petrol [L]. Pokud by zde přibyl další list s názvem Totaly_2 tak opět bude obsahovat auta, která nejsou v Totaly_X a zase je budu potřebovat připsat do listu totaly_X.

předem díky za pomoc.

Luboš

[Reklama]

[Grimm]

Řešení pomocí makra (VBA). Makro spustíš přes kartu Vývojář -> makra a spustit nebo ALT+F8 a spustit. Makro prochází všechny listy v sešitě a porovnává hodnoty sloupce A. pokud narazí na hodnotu, která není na listě totaly_x překopíruje celý řádek.
OMEZENÍ: Na listě totaly_ x musí být alespoň jeden záznam, aby mohlo dojít k porovnání.

Verze2: To samé jako předchozí s tím rozdílem, že pokud je list totaly_x prázdný (obsahuje jen hlavičku) překopíruje celý obsah listu následujícího za listem totaly_x a poté porovnává hodnoty z dalších listů.

Ani v jednom listu sem neřešil případ, kdy by list s názvem totaly_x chyběl nebo neměl hlavičku. Nepředpokládám že by tato situace mohla nastat pokud budeš chtít je možné to dodělat.

rent_care.xlsm

(21.23 KiB) Staženo 82 x

rent_care rev.2.xlsm

(23.06 KiB) Staženo 70 x

[Rent_cars]

Dobrý den,

Děkuji za pomoc. Podívám se na to a dám vědět :).

Luboš

[Rent_cars]

Ahoj,

tak jsem aplikoval kód na originální soubor a jede jak vyšitý :).

EDIT:

Ahoj,

Tvůj kód projíždí všechny listy, od prvního až po poslední. Potřeboval bych ale, aby projíždění začal na druhém listu (na prvím je taková tabulka shrnutí) a končil na třetím. Po čase na čtvrtém (až přibyde), na pátem, atd... vždy když přibyde list, přepíšu cyklus z 3 na 4 ... z 4 na 5... atd

Udělal jsem tuto úpravu v cyklu:

Tvůj kód:

Kód: Vybrat vše

For L = 1 To ActiveWorkbook.Sheets.Count
    JmenoListu = ActiveWorkbook.Sheets(L).Name
   
    If JmenoListu = "totaly_X" Then GoTo dalsi

má úprava:

Kód: Vybrat vše

For L = 2 To 3
    JmenoListu = ActiveWorkbook.Sheets(L).Name
   
    If JmenoListu = "totaly_X" Then GoTo dalsi

Zbytek programu jsem nechal stejný.

Program se dokončí, vyskočí message box s textem "dokopírováno", ale data se do listu totaly_X nenakopírují.

Mohu vůbec takovou úpravu cyklu udělat? Musím ještě nějak dále upravit další kód?

Moc děkuji

[Grimm]

Sorry za pozdní reakci, ale Tvůj edit sem zaregistroval až dnes.
Ano máš pravdu kód projíždí listy od prvního do posledního tedy i "totaly_X", který je vyřazen z kopírování. Nebylo by tedy lepší do kódu zapsat jména listů, které se mají být vyřazeny, místo neustálého přepisování kódu?

Něco jako tohle:

Kód: Vybrat vše

For L = 1 To ActiveWorkbook.Sheets.Count
    JmenoListu = ActiveWorkbook.Sheets(L).Name
    Select Case JmenoListu
     '***************************************
     'Jména listů vyřazených z kopírování
        Case "totaly_X", "totaly_1"
     '***************************************
            GoTo dalsi
    End Select


Místo jména listu "totaly_1" zapíšeš jméno listu kde je tabulka shrnutí.

[Rent_cars]

Díky za reakci :).

Jako, ano, jednodušší by to asi bylo, ale narážím spíše na ten problém, že se ty data nepřekopírujou. Když bychom zůstali u té změny kterou jsem udělal já, tak by měl kód fungovat? Není třeba ještě něco upravit? Protože mí kód skončí, vyskočí hláška "dokopírováno", ale na listu totaly_X se data neoběví.

EDIT:

Zjistil sem toto:

V listě totaly_X mam tabulku. Pojmenoval jsem si tu tabulku tabulka_X - ta tabulka, která obsahuje data o autech, lidech... prostě celá tabulka která se nachází v listě totaly_X. Udělal jsem to pro lepší práci s adresováním. Když ale tuto tabulku rozšířím třeba o 50 řádků, tak pak už import funguje. Jenže ja ji moc rozsirovat nechci, protoze kdyz ji rozšířím, tak se všude kde mám funkci SVYHLEDAT napíše nějaká chyba a zabírát to hrozně moc místa (soubor s origo daty má 9mb a poroste s každým listem). Navíc nevím, kolik se těch řádků z listů totaly_1 a totaly_2..._3 překopíruje. Musel bych tam tedy na jistotu dát rezeruvu třeba 50 000 řádků a pak se cely excel zasekne. Šlo by nějak v kódu ošetřit, aby se ta tabulka_X vždy zvětšila jen o ten počet řádků, které se tam musejí přikopírovat?

Je mi rozumět? Možná trochu plantám... Pokud by nebylo rozumět napiš, a zkusím to vysvětlit pečlivěji.

Diky moc :)

[Grimm]

Ahoj, měnit rozsah listů "lze" změnou v cyklu For L = 2 to 3, není nutné upravovat jinou část kódu. Vyzkoušeno. Ale jak už sem psal, pokud chceš nějaké listy z cyklu vyřadit je vhodné využití select case. Předejde se případným problémům v případě změny pořadí jednotlivých listů.

Problém nejspíše bude v té Tvé "tabulce_x", můžeš poskytnout vzorek? Kouknul bych na to.

[Rent_cars]

Zdravím.

přikládám soubor, ve které, v listě totaly_X je tabulka a jmenuje se tabulka_x. Lis s názvem totaly_1, obsahuje tabulku, která se jmenuje tabulka_1. Obě tabulky mají přesně danou oblast - celá tabulka. Tady v tomto ukázkovém případě přidání řádků funguje... je mi záhadou jak to, ale v mém případě, mám situaci uplně stejnou a řádky se nepřipíšou

Mám 4 listy.

První je Sumarizační tabulka (nebrát v potaz)
Druhý je Totaly_X
Třetí je Totaly_1
Čtvrý je pomocná tabulka (nebrát v potaz)

Časem přibydou listy totaly_2 ....

Když ale spustím program, chvilku šrotujem vyskočí okno - dokopírováno, ale (cca 2000) řádků se z totaly_1 do totaly_x nepřekopíruje. Překopíruje se to pouze tehdy, pokud zvětším oblast tabulky_x. Třeba na 50000 řádků.. nebo prostě o tolik, aby se tam vešly řádky z tabulka_1. Jenže pokud již dopředu oblast zvětším, tak (jelikož tam mám funkci SVYHLEDAT) se všude kde je ta funkce natažená dopíšou chyby a zabírá to třeba o 4 Mb více a soubor je poté strašně pomalý (nachází se na síti.).

Jelikož kopírování na vzorových datech funguje, vypadá to, že kód je v pořádku. Nenapadá Tě, proč mi v popsaném případě kopírování nefunguje?

Díky moc.

Odpověď na předhozí příspěvek:

Co se týče nápadu s vynecháním listů tak jde o to, že časem bude mít každý list třeba 10000, 15000 a více či méně řádků. A kdyby kód procházel všchny listy pořád dokola, trvalo by to věčnost a bylo by to zbytečné. Protože již jedou se list prošel, nakopírují se řádky a není potřeba, aby při přidání ndalšího listu se procházel znova. Proto bych to dělal tak, že bych list, který je nový, dal dočasně hned za totaly_X - cyklus Dal L= 2 To 3 a tím by se projel jenom jeden konkrétní list a žádné navíc.

[Grimm]

Ahoj, tabulka je vlastně prvek ListObject, se kterým sem do této doby ve VBA neměl tu čest a bohužel musím přiznat, že nejsem schopen přinutit ho, aby se choval jak chci. Jak už si psal ve vzorovém souboru kopírování řádků "funguje", ale jakmile se začne manipulovat s řádky dochází k problémům
Pokud kopírování proběhne tabulka se neroztáhne jako je tomu v případě ručního zápisu, pokud se data z tabulky odstraní (delete, zůstane pouze hlavička) "Tabulka" (ListObject) zůstává na řádcích a další data se kopírují pod ní.

Za sebe tedy můžu k tomuto problému říct = nepoužívat prvek ListObject (Tabulka)

Snad se do tohoto vlákna připojí někdo, kdo má s tímto prvkem ve VBA více zkušeností a dokáže poradit.

Co se týče procházení listů:
Budu se opakovat, ale použil bych do kódu již zmíněné Select Case. Kam by se zapsala jména listů, která mají být vynechána z kopírování (sumarizační tabulka, pomocná tabulka ...) a teď je několik možností, při překopírování dat z listu (např. totaly_1) se jméno listu zapíše buď někam bokem na list totaly_X nebo na jiný list (např. seznam listů, který se vytvoří ručně nebo makrem. Tento list může mít nastaven atribut na skrytý nebo velmi skrytý). Při dalším spuštění kopírování se porovná takto vzniklý seznam, pokud bude jméno listu v tomto seznamu uvedeno list bude přeskočen = není nutné editovat kód aby se zabránilo procházení listů, které již byly nakopírovány.

[Rent_cars]

Ahoj,

díky za odpověď .

Tento problém s kopírováním řádků je poslední, který mi braní v nasazení systému v půjčovně :(. Tento ukol jsem převzal po kolegovi, který většinu práce udělal (včetně zmíněných objectListu) a upřímně bych byl radši, kdyby se našlo řešení, než to celé předělávat. Ale chápu, že jsi se s tím nesetkal a pak je těžko radit :). každopádně moc děkuji za program co jsi napsal. Pro dosavadní testování to stačí, protože si natáhnu tabulku o požadovaný počet řádků. Ovšem do budoucna, bych potřeboval, aby bylo kopírování automatické. Možná že se někdo najde... :).

Co se týče procházení, ano, máš pravdu. Nyní potřebuji ale s nejvyšší prioritou zajistit kopírování. Až to bude fungovat, budu zkoušet, zda je jednodušší přesunout list na třetí pozici, nebo využít funkce case...

Našel by se prosím tady nějaký expert, který by si s popsaným problémem věděl rady??

Předem moc díky :).

[elninoslov]

"Našel"
Riešené cez Collection. Na skúšku som použil 4 listy, z toho 2 majú po 20000 jedinečných záznamov. Nakopírovanie 39994 riadkov trvá asi 1 s. Vyskúšajte. A vzhľadom na rýchlosť, by možno ani nebolo treba riešiť už použité listy. Záleží na tom koľko ich bude...
V prílohe je aj malý súbor "takmer" bez dát.

PS: V tom predošlom riešení od kolegu by to aj tak vždy havarovalo, ak by sa dosiahol limit 256 znakov v stringu pre Range. Čo je tak asi 100 adries riadkov.

[Rent_cars]

Ahoj :)

díky moc za rychlou reakci.

Program vypadá skvěle. Rychlost je naprosto parádní.

Jelikož se ale nyní snažím program napasovat na databázi půjčovny, tak jsem narazil na jednu věcm kvůli které mi program nechce jít dále.

Pochopil jsem, že do 13. sloupce si napíšu listy, které chci ignorovat a ty se v načítaní a porovníní dat budou ignorovat.

Tvůj kód:

Kód: Vybrat vše

Sub Porovnaj_a_kopiruj()
Dim Pole(), LOX As ListObject, LO As ListObject, Col As New Collection, ColX As New Collection, R As Long, i As Long, LOXr As Long, WS As Worksheet, IgnR As Long, nIgnR As Long, IgnL As String, nIgnL As String, C As Variant

  With ThisWorkbook
    With wsTotalyX
      IgnR = .Cells(Rows.Count, 13).End(xlUp).Row - 1
      IgnL = "•"

Já bych potřeboval, aby byl ten sloupce s vynechanými listy na sloupci 52 (BB).

Pak zde máš napsano

Kód: Vybrat vše

With wsTotalyX

- jaký to je prosím objekt? List se jmenuje Totaly_X a tabulka tabulka_X. V tvem With chybí podtržítko a funguje to. Jakto?

Dále mě prosím zajímá toto. Ve vzorových datech mám nějakých 9 sloupců. V originální databázi jich je asi 15... Nepovažoval jsem je za důležité, proto jsem je do vzorových dat nepsal. Počítá tvůj kod s tím, že má každý list 9 sloupců? Nebo jenom porovná klíče a pak zkopíruje řádek jako celek, nezávisle na počtu sloupců?

Nyní se nemoho dostat právě přes ten kousek kodu, co jsem napsal. Napsal jsem si toto (list s přehledem se v databázi jmenuje Database)

Kód: Vybrat vše

Sub Porovnaj_a_kopiruj()
Dim Pole(), LOX As ListObject, LO As ListObject, Col As New Collection, ColX As New Collection, R As Long, i As Long, LOXr As Long, WS As Worksheet, IgnR As Long, nIgnR As Long, IgnL As String, nIgnL As String, C As Variant

  With ThisWorkbook
    With wsDatabase
      IgnR = .Cells(Rows.Count, 52).End(xlUp).Row - 1
      IgnL = "•"

A právě na řádku: IgnR = .Cells(Rows.Count, 52).End(xlUp).Row - 1

mi vyskočí chyba - Object required...

Díky moc :))