Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Suchen und Kopieren über mehre Arbeitsblätter

Betrifft: Suchen und Kopieren über mehre Arbeitsblätter von: Benny
Geschrieben am: 05.11.2014 11:54:51

Hallo Liebe VBA’ler

neulich hatte ich „Hilfe“ bei einem Quellcode, diesen wollte ich für ein neues Projekt modifizieren.
Damals wollte ich eine bestimmte Seite durchsuchen, nun möchte ich, dass alle Seiten durchsucht werden. Leider führt „For Each QuellSheet In Worksheets“ zu dem Fehler „Die Methode Union für das Objekt _Global ist fehlgeschlagen. Könnt Ihr mir dabei Helfen?
Außerdem habe ich noch eine Frage ist es möglich den Quellcode so anzupassen das nur bestimmte Spalten einer Zeile kopiert werden?

Sub Kopiern_VerR()
Dim Zeile As Long
Dim ZeileMax As Long
Dim n As Long
Dim Projekt
Dim Quell As Workbook, QuellSheet As Worksheet, rngQuelle As Range
Dim rng As Range

On Error GoTo ErrorHandler:
Application.ScreenUpdating = False
Application.EnableEvents = False
Projekt = Tabelle1.Range("A1").Value
n = ActiveCell.Row

Set Quell = Workbooks.Open("R:\Allgemein\2014.xlsx")
For Each QuellSheet In Worksheets 
        With Tabelle1 
        ZeileMax = QuellSheet.UsedRange.Rows.Count
            For Zeile = 2 To ZeileMax
            If QuellSheet.Cells(Zeile, 10).Value = Projekt Then
                If Not rngQuelle Is Nothing Then
                    Set rngQuelle = Union(rngQuelle, QuellSheet.Rows(Zeile))
                Else
                    Set rngQuelle = QuellSheet.Rows(Zeile)
                End If
            End If

        Next Zeile
    
        If Not rngQuelle Is Nothing Then
            rngQuelle.Copy
            .Rows(n).PasteSpecial (xlPasteAll) 
            .Rows(n).PasteSpecial (xlPasteValues) 
            Application.CutCopyMode = False
    End If

End With

Next QuellSheet 'erzeugt fehler
ErrorHandler:

If Not Quell Is Nothing Then Quell.Close savechanges:=False

Application.EnableEvents = True
Application.ScreenUpdating = True

If Err.Number <> 0 Then
    MsgBox Err.Description, _
           vbCritical + vbMsgBoxSetForeground + vbMsgBoxHelpButton, _
           "Error: " & Err.Number, Err.HelpFile, Err.HelpContext
End If


End Sub

  

Betrifft: AW: Suchen und Kopieren über mehre Arbeitsblätter von: Frank
Geschrieben am: 05.11.2014 19:37:25

Hallo Benny,

ohne Deklarationen und weiteren Code funktioniert das hier bei mir prima:

Sub test1()
For Each QuellSheet In Worksheets
    MsgBox QuellSheet.Name
Next QuellSheet
End Sub

Bist Du Dir sicher, dass der Fehler dadurch erzeugt wird? Was ist denn gelb markiert im Editor, wenn Du bei der Fehlermeldung den Button 'Debuggen' drückst?

Grüsse,
Frank


  

Betrifft: AW: Suchen und Kopieren über mehre Arbeitsblätter von: Benny
Geschrieben am: 06.11.2014 09:03:47

Hi Frank,

das Makro erzeugt einen Fehler in Excel, nicht in VBA darum kann ich leider nicht Debug drücken.
Die Zeilen auf der ersten Seite die einen Treffer enthält werden kopiert, allerdings bricht das Makro dann ab mit der Fehlermeldung „Die Methode Union für das Objekt _Global". Ich habe den Verdacht, dass das Problem in der Kombination von Union und der For Each QuellSheet In Worksheetsfunktion liegt. Beides alleine Funktioniert nur zusammen leider nicht. Wie kann ich dieses Problem umgehen?
Oder Irre ich mich?


  

Betrifft: Kenne mich mit UNION nicht aus von: Frank
Geschrieben am: 06.11.2014 10:33:53

Hallo Benny,

mit UNION kenne ich mich nicht aus. Die Online-Hilfe hilft nicht sehr, die Verwendung sieht da im Detail aber auch anders aus, als Du es machst: Set bigRange = Application.Union(Range("Range1"), Range("Range2"))
Was ich mir noch vorstellen könnte ist, dass die Bereiche gleiche Formatierungen und Inhalte haben müssen. Aber das ist geraten. Was soll denn mit UNION erreicht werden?

Grüsse,
Frank


  

Betrifft: AW: Kenne mich mit UNION nicht aus von: Benny
Geschrieben am: 06.11.2014 16:04:11

Hi Frank,

der Union-befehl stammt von Tino (https://www.herber.de/forum/archiv/1388to1392/t1388137.htm), soweit ich verstanden habe sorgt er dafür, dass die zu kopierenden Daten "zusammengetragen" werden. Im Prinzip soll das Makro, dass ich gerade benötige ähnlich wie jenes davor nach der Variable Projekt suchen und die Treffer kopieren. Der große Unterschied liegt eigentlich nur darin das ich jetzt mehrere Tabellenblätter habe, die Treffer beinhalten können und nicht alles kopiert werden soll. Ich habe mir das am Anfang als einfache Modifikation vorgestellt *G*. Ich bin aber auch für ganz andere Lösungsvorschläge offen.


  

Betrifft: Kristallkugel... von: Frank
Geschrieben am: 06.11.2014 17:01:17

Hallo Benny,

ähem... ja, dann am besten neuen Thread eröffnen, Beispielmappe einstellen und auf Antwort hoffen.

Ein bisschen Vorarbeit kannst Du schon leisten. Mein Codeschnipselchen durchläuft tatsächlich alle Blätter der Mappe. Statt des Anzeigens der Msgbox mit den Blattnamen, könnte da Dein Code stehen.

Mit WertZelle1=Sheets(Name/Index).cells(ZeilenNr,SpaltenNr).value weist Du den Inhalt einer Zelle einer Variablen zu. Mit Sheets(Name/Index).cells(ZeilenNr,SpaltenNr).value=WertZelle1 machst Du das Gegenteil, einer Zelle den Wert einer Variablen zuweisen.
Jetzt musst Du nur noch ausgelesene Werte mit Vorgabewerten vergleichen und entsprechend des Ergebnisses Aktionen starten.

Mit

for i=1 to 10
   for j=1 to 10
      msgbox "Zeile: " & i & " / Spalte: " & j & " / Wert: " & cells(i,j).value
   next
next

wird Dir von Zeile 1 bis Zeile 10 und Spalte A bis Spalte J der Inhalt der Zellen in einer Msgbox ausgegeben.

Mehr brauchst Du eigentlich nicht.

Grüsse,
Frank


 

Beiträge aus den Excel-Beispielen zum Thema "Suchen und Kopieren über mehre Arbeitsblätter"