Czasami chcemy scalić wiele arkuszy w jeden, aby móc łatwo przeanalizować dane i przekształcić je w przydatne informacje. W tych artykułach dowiesz się, jak połączyć wiele arkuszy roboczych w jeden arkusz roboczy za pomocą VBA.
Przykład:
Tutaj pobrałem dane z serwera, który zwraca dane do różnych arkuszy. Dodałem jeszcze jeden arkusz i nazwałem go „Master”. Inne nazwy arkuszy nie mają znaczenia.
Teraz uruchom to makro.
Sub Merge_Sheets() Dim startRow, startCol, lastRow, lastCol As Long Dim headers As Range 'Ustaw arkusz główny do konsolidacji Set mtr = Worksheets("Master") Set wb = ThisWorkbook 'Get Heads Set headers = Application.InputBox("Wybierz Headers", Type:=8) 'Kopiuj nagłówki do nagłówków głównych.Copy mtr.Range("A1") startRow = headers.Row + 1 startCol = headers.Column Debug.Print startRow, startCol 'przejdź przez wszystkie arkusze For Each ws In wb.Worksheets 'z wyjątkiem arkusza głównego przed zapętleniem If ws.Name "Master" Then ws.Activate lastRow = Cells(Rows.Count, startCol).End(xlUp).Row lastCol = Cells(startRow, Columns.Count). End(xlToLeft).Column 'pobierz dane z każdego arkusza i skopiuj je do arkusza głównego Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr.Cells(Rows) .Count, 1).End(xlUp).Row + 1) End If Next ws Worksheets("Master").Aktywuj End Sub
Jak scalić arkusze za pomocą tego makra VBA?
- Wstaw nowy arkusz i nazwij go „Master” w skoroszycie. Zmień nazwę później, jeśli chcesz.
- Wstaw moduł do edytora VBA i skopiuj powyższy kod VBA.
- Uruchom makro.
- Zostaniesz poproszony o wybranie nagłówków. Wybierz nagłówek i naciśnij OK.
I gotowe. Wszystkie arkusze są scalane w wzorcu.
Jak to działa?
Zakładam, że znasz podstawy tworzenia obiektów i zmiennych w VBA. w pierwszej części stworzyliśmy obiekt i zmienne, których będziemy potrzebować w naszych działaniach.
Cóż, większość rzeczy, które wyjaśniłem za pomocą komentarzy w kodzie vba. Spójrzmy na główną część tego kodu vba.
For Each ws In wb.Worksheets 'z wyjątkiem arkusza głównego przed zapętleniem If ws.Name "Master" Then ws.Activate lastRow = Cells(Rows.Count, startCol).End(xlUp).Row lastCol = Cells(startRow, Columns. Count).End(xlToLeft).Column 'pobierz dane z każdego arkusza i skopiuj je do arkusza głównego Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr. Cells(Rows.Count, 1)).End(xlUp).Row + 1) End If Next ws
We wcześniejszych artykułach dowiedzieliśmy się, jak przechodzić przez arkusze i jak uzyskać ostatni wiersz i kolumnę za pomocą vba.
Tutaj przechodzimy przez każdy arkusz w głównym skoroszycie za pomocą pętli for.
For Each ws In wb.Worksheets
Następnie wyłączamy arkusz „master” z pętli, ponieważ będziemy konsolidować nasze dane w tym arkuszu.
Następnie otrzymujemy numer ostatniego wiersza i ostatniej kolumny.
Teraz kolejna linijka jest bardzo ważna. Wykonaliśmy wiele operacji w jednej linii.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
Najpierw tworzymy zakres za pomocą startRow, startCol oraz lastRow i lastCol.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)) Skopiowaliśmy go metodą kopiowania zakresu. Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Kopiuj Wkleiliśmy go bezpośrednio do pierwszej pustej komórki po ostatniej niepustej komórce w kolumnie A arkusza głównego (mtr.Cells(Rows.Count, 1).End (xlUp).Wiersz + 1). Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _ mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
Ta pętla działa dla wszystkich arkuszy i kopiuje dane każdego arkusza do arkusza głównego.
Na koniec na końcu makra aktywujemy arkusz wzorcowy, aby zobaczyć wynik.
Więc tak, w ten sposób można scalić każdy arkusz w skoroszycie. Daj mi znać, jeśli masz jakiekolwiek pytania dotyczące tego kodu VBA lub dowolnego tematu programu Excel w sekcji komentarzy poniżej.
Pobieranie pliku:
Powiązane artykuły:
Jak zapętlić prześcieradła
jak uzyskać ostatni wiersz i kolumnę za pomocą vba
Usuń arkusze bez monitów o potwierdzenie za pomocą VBA w programie Microsoft Excel
Dodaj i zapisz nowy skoroszyt za pomocą VBA w programie Microsoft Excel 2016
Wyświetl komunikat na pasku stanu Excel VBA
Wyłącz komunikaty ostrzegawcze za pomocą VBA w programie Microsoft Excel 2016
Popularne artykuły:
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?