in einem anderen Registerblatt in eine freie Zelle in bestimmten Spaltenbereich springen

Question

Hallo in die Runde der Experten

Ich habe auf einem Registerblatt viele Zeilen mit Zahlen, welche sich in nebeneinanderliegenden Spalten befinden (so  zw. 30 und 40, event auch einmal mehr)

Auf diesem Registerblatt befindet sich ein Button welcher nachfolgende Routine auslöst

Private Sub CommandButton1_Click()

Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

Application.Goto Sheets("Lager").Range("AU5“)

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Nach dem Anklicken einer Zelle im ersten Registerblatt und der Click auf den CB 1 macht die Routine erst einmal was sie soll. Markiert, Kopiert, springt ins Registerblatt „Lager“ und fügt dort die Zahlen aus dem Zwischenspeicher brav in Zelle "AU5" beginnend nach rechts ein.

Jetzt mein Problem:
per Excel-VBA soll den Inhalt des Zwischenspeichers immer in die erste freie Zelle (von oben nach unten betrachtet)  in Spalte AU (=Spalte 47) und zwar nur im Bereich von Zeile 5 bis 35 einfügen.
Nur bis Zeile 35, weil darunter schon Eintragungen vorhanden sind, welche nicht überschrieben werden dürfen. Also nicht starr immer von Zelle AU5 (im Registerblatt „Lager“ nach rechts.

Vlt. kann man das noch absichern, für den Fall, dass in Zeile AU35 schon etwas drinsteht – nicht dass das Programm sich deswegen aufhängt.
Und wenn möglich, dass die Zelle BG41 auf Registerblatt „Lager“ selektiert wird (quasi dort den Cursor »parkt«).

Vorab schon mal allen ein Dankeschön fürs Lesen und event. Posten von einem Lösungsvorschlag.

Ciao der Revanchist

Answer 1

Hallo Revanchist,

der folgende Code sollte so funktionieren, wie du es willst:

Private Sub CommandButton1_Click()
Dim lngEinf As Long
Dim lngZeile As Long

'Bildschirmaktualisierung ausschalten:
Application.ScreenUpdating = False

'Bereich kopieren
Range(Selection, Selection.End(xlToRight)).Copy

With Worksheets("Lager")
  'im Arbeitsblatt Lager in Spalte AU die Zeilen 5 bis 35 leere Zeile für das Einfügen der Daten suchen
  For lngZeile = 5 To 35
    If .Cells(lngZeile, 47) = "" Then
       lngEinf = lngZeile
       Exit For
    End If
  Next lngZeile
  If lngEinf = 0 Then
    'falls keine Einfügezeile mehr frei ist, dann Fehlermeldung
    MsgBox "Achtung! Im Tabellenblatt Lager ist der Einfügebereich voll! Abbruch!", 16, "Fehler"
    'getroffene Auswahl aufheben
    Application.CutCopyMode = False
    'Makro beenden
    Exit Sub
  End If
  
  'Daten einfügen
  .Cells(lngEinf, 47).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  'getroffene Auswahl aufheben
    Application.CutCopyMode = False
    
   'Zelle BG41 selektieren
   'dazu zuerst das Tabellenblatt aktivieren
   .Activate
   .Range("BG41").Select
    
End With

'Bildschirmaktualisierung einschalten:
Application.ScreenUpdating = True

End Sub

Gruß

M.O.

P.S. Änderung wegen Denkfehler 

Answer 2

Hallo M.O.

vielen Dank für deine tolle Lösung !

ich war mit deinem zuerst gepostetem Code schon ein wenig am verzweifeln.
Immer wieder die Meldung "Fehler beim Kompilieren" entweder »ungültiges Zeichen« oder »Syntaxfehler« beim Befehl
.Cells(lngLetzte, 47).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
Aber die korrigierten Fassung ist jetzt genau das was ich gesucht habe - DANKE!

Ciao der Revanchist

ist schon ein tolles Forum !