VBA letzte befuellte Zelle einer Spalte ermitteln und Range kopieren
Volker Neurath
meine �berschrift ist etwas ungenau.
Was ich m�chte, schildere ich an einem Beispiel:
In ein Excel-Sheet werden Produktlisten eingetragen, mit verschiedenen
Werten. die Spalte A enth�lt dabei die Produktbeschreibung, die Spalte B
die Artikelnummer.
Ene VBA-routine soll nun die in spalte A und B eingetragenen Werte in
ein zweites Blatt desselben Workbooks kopieren.
Klar k�nnte ich das einfach in Zelle A2 des Zielsheets
=Wenn(ISTLEER(Blatt1!A2;"";Blatt1!A2)
schreiben und runterkopieren bis Blattende - aber das finde ich
unelegant.
Ich m�chte halt, dass nur soviele Zeilen kopiert werden, wie auch
vorhanden sind.
Au�erdem ben�tige ich diese Funktion ein weiteres mal, da aus derselben
Liste noch ein ExcelSheet erstellt werden muss, das in unser WW-System
hochgeladen wird; und da w�rden z.B. Formeln st�ren.
Volker
--
Wenn es vom Himmel Zitronen regnet -- lerne, Limonade zu machen.
Andreas Killer
> In ein Excel-Sheet werden Produktlisten eingetragen, mit verschiedenen
> Werten. die Spalte A enth锟絣t dabei die Produktbeschreibung, die Spalte B
> die Artikelnummer.
>
> Ene VBA-routine soll nun die in spalte A und B eingetragenen Werte in
> ein zweites Blatt desselben Workbooks kopieren.
Wann?
Soll direkt bei der Eingabe das Kopieren stattfinden oder m锟絚htest Du
selber mit einem Makro generell alle Wert in Spalte A und B kopieren?
> Au锟絜rdem ben锟絫ige ich diese Funktion ein weiteres mal, da aus derselben
> Liste noch ein ExcelSheet erstellt werden muss, das in unser WW-System
> hochgeladen wird; und da w锟絩den z.B. Formeln st锟絩en.
Also soll das Kopieren nicht nur in ein sondern in 2 Bl锟絫ter erfolgen?
Wie hei锟絜n die Bl锟絫ter denn eigentlich?
Tjajaja, ich glaube nicht nur Deine 锟絙erschrift ist ungenau. ;-)
Andreas.
Sub Test()
Dim Ymax As Long
'Letzte Zeile in Spalte A und B ermitteln, _
und h锟絟ere Zeile bestimmen.
Ymax = WorksheetFunction.Max( _
Range("A" & Rows.Count).End(xlUp).Row, _
Range("B" & Rows.Count).End(xlUp).Row)
'Alles von Zelle A1 bis B[letzte Zeile] kopieren _
nach Tabelle2
Range(Range("A1"), Range("B" & Ymax)).Copy _
Destination:=Sheets("Tabelle2").Range("A1")
End Sub
Volker Neurath
> Volker Neurath schrieb:
>> In ein Excel-Sheet werden Produktlisten eingetragen, mit verschiedenen
>> Werten. die Spalte A enth锟絣t dabei die Produktbeschreibung, die Spalte B
>> die Artikelnummer.
>>
>> Ene VBA-routine soll nun die in spalte A und B eingetragenen Werte in
>> ein zweites Blatt desselben Workbooks kopieren.
> Wann?
> Soll direkt bei der Eingabe das Kopieren stattfinden
Am liebsten, ja.
Das gesamte Worksheet soll demn锟絰t an unsere Aussendienstler gehen;
denen m锟絚hte ich nur notfalls noch zumuten, im Zielsheet einen Button
dr锟絚ken zu m锟絪sen
>> Au锟絜rdem ben锟絫ige ich diese Funktion ein weiteres mal, da aus derselben
>> Liste noch ein ExcelSheet erstellt werden muss, das in unser WW-System
>> hochgeladen wird; und da w锟絩den z.B. Formeln st锟絩en.
> Also soll das Kopieren nicht nur in ein sondern in 2 Bl锟絫ter erfolgen?
> Wie hei锟絜n die Bl锟絫ter denn eigentlich?
Quellsheet: Produktliste
Zielsheet 1: Competitor Information
Zielsheet 2: Uploadliste
Relevante Informationen aus dem Quellsheet
F锟絩 Zielsheet 1: spalten A und B ab A4 bzw B4 - und dann eben bis n.
F锟絩 Zielsheet 2: Spalten A - V ohne spalte L
> Tjajaja, ich glaube nicht nur Deine 锟絙erschrift ist ungenau. ;-)
Is ja gut ;)
> Sub Test()
> Dim Ymax As Long
> 'Letzte Zeile in Spalte A und B ermitteln, _
> und h锟絟ere Zeile bestimmen.
> Ymax = WorksheetFunction.Max( _
> Range("A" & Rows.Count).End(xlUp).Row, _
> Range("B" & Rows.Count).End(xlUp).Row)
> 'Alles von Zelle A1 bis B[letzte Zeile] kopieren _
> nach Tabelle2
> Range(Range("A1"), Range("B" & Ymax)).Copy _
> Destination:=Sheets("Tabelle2").Range("A1")
> End Sub
Sieht gut aus, damit sollte ich den Rest alleine hinbekommen - bis auf
kopieren w锟絟rend der eingabe.
Danke
Volker Neurath
> Sub Test()
> Dim Ymax As Long
> 'Letzte Zeile in Spalte A und B ermitteln, _
> und h�here Zeile bestimmen.
> Ymax = WorksheetFunction.Max( _
> Range("A" & Rows.Count).End(xlUp).Row, _
> Range("B" & Rows.Count).End(xlUp).Row)
> 'Alles von Zelle A1 bis B[letzte Zeile] kopieren _
> nach Tabelle2
> Range(Range("A1"), Range("B" & Ymax)).Copy _
> Destination:=Sheets("Tabelle2").Range("A1")
> End Sub
Hab den code Ausprobiert.
Setze ich den Beginn auf Zelle "A4" (dort beginnt mein Wertebereich)
wird nichts kopiert, setze ich den Beginn auf A1, wird nurdie
�berschritenZeile (eben die Zellen A1 und B1) kopiert.
Volker Neurath
Private Sub cmdProduktlisteKopieren_Click()
Dim Ymax As Long
'Letzte Zeile in Spalte A und B ermitteln, _
und h�here Zeile bestimmen.
Ymax = WorksheetFunction.Max( _
Sheets("Produktliste").Range("A" & Rows.Count).End(xlUp).Row, _
Sheets("Produktliste").Range("B" & Rows.Count).End(xlUp).Row)
Debug.Print
'Alles von Zelle A1 bis B[letzte Zeile] kopieren _
nach Tabelle2
Range(Sheets("Produktliste").Range("A4"),
Sheets("Produktliste").Range("B" & Ymax)).Copy _
Destination:=Sheets("Competitor Information").Range("A3")
End Sub
erhalte ich einen laufzeitfehler mit "Die Methode Range f�r das Objekt
Worksheet ist fehlgeschlagen"
Ich weiss nicht mehr weiter und gebe hiermit f�r heute auf :(
Montag gehts weiter :(
Claus Busch
dann probiers mal so:
Private Sub cmdProduktlisteKopieren_Click()
Dim Ymax As Long
'Letzte Zeile in Spalte A und B ermitteln
'und h�here Zeile bestimmen.
With Sheets("Produktliste")
Ymax = WorksheetFunction.Max( _
.Range("A" & Rows.Count).End(xlUp).Row, _
.Range("B" & Rows.Count).End(xlUp).Row)
Debug.Print
'Alles von Zelle A4 bis B[letzte Zeile] kopieren
'nach Tabelle2
.Range(Cells(4, 1), Cells(Ymax + 3, 2)).Copy _
Destination:=Sheets("Competitor Information").Range("A3")
End With
End Sub
Mit freundlichen Gr�ssen
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Volker Neurath
> Hallo Volker,
> dann probiers mal so:
> Private Sub cmdProduktlisteKopieren_Click()
> Dim Ymax As Long
> 'Letzte Zeile in Spalte A und B ermitteln
> 'und h�here Zeile bestimmen.
> With Sheets("Produktliste")
> Ymax = WorksheetFunction.Max( _
> .Range("A" & Rows.Count).End(xlUp).Row, _
> .Range("B" & Rows.Count).End(xlUp).Row)
> Debug.Print
> 'Alles von Zelle A4 bis B[letzte Zeile] kopieren
> 'nach Tabelle2
> .Range(Cells(4, 1), Cells(Ymax + 3, 2)).Copy _
> Destination:=Sheets("Competitor Information").Range("A3")
> End With
F�hrt zu "Anwendungs- oder objektdefinierter Fehler", makriert ist:
> .Range(Cells(4, 1), Cells(Ymax + 3, 2)).Copy _
> Destination:=Sheets("Competitor Information").Range("A3")
Claus Busch
Am Fri, 14 May 2010 16:45:51 +0200 schrieb Volker Neurath:
> F�hrt zu "Anwendungs- oder objektdefinierter Fehler", makriert ist:
>
>> .Range(Cells(4, 1), Cells(Ymax + 3, 2)).Copy _
>> Destination:=Sheets("Competitor Information").Range("A3")
sorry, habe die Punkte vergessen. �ndere so ab:
.Range(.Cells(4, 1), .Cells(Ymax + 3, 2)).Copy _
Destination:=Sheets("Competitor Information").Range("A3")
Volker Neurath
>> Fï¿œhrt zu "Anwendungs- oder objektdefinierter Fehler", makriert ist:
>>
>>> .Range(Cells(4, 1), Cells(Ymax + 3, 2)).Copy _
>>> Destination:=Sheets("Competitor Information").Range("A3")
>
> sorry, habe die Punkte vergessen. ï¿œndere so ab:
> .Range(.Cells(4, 1), .Cells(Ymax + 3, 2)).Copy _
> Destination:=Sheets("Competitor Information").Range("A3")
Etwas zu schnell auf "Senden" geklickt:
was mï¿œsste ich denn tun, damit die ï¿œbertragung schon wï¿œhrend der Eingabe
erfolgt?
Geht das ï¿œberhaupt?
Volker
--
Im ï¿œbrigen bin ich der Meinung, dass TCPA/TCG verhindert werden muss
Wenn es vom Himmel Zitronen regnet, dann lerne, wie man Limonade macht
Volker Neurath
>>> .Range(Cells(4, 1), Cells(Ymax + 3, 2)).Copy _
>>> Destination:=Sheets("Competitor Information").Range("A3")
>
> sorry, habe die Punkte vergessen. ï¿œndere so ab:
> .Range(.Cells(4, 1), .Cells(Ymax + 3, 2)).Copy _
> Destination:=Sheets("Competitor Information").Range("A3")
Uff!
Den fehlenden Punkt habbich auch ï¿œbersehen.
OK, ich teste das am Montag, jetzt hab ich erstmal Wochenende (und zu Hause
kein Excel)
Claus Busch
Am Fri, 14 May 2010 19:01 +0200 schrieb Volker Neurath:
> was m�sste ich denn tun, damit die �bertragung schon w�hrend der Eingabe
> erfolgt?
du m�sstest in "Produktliste" Ver�nderungen an den Zellen �berwachen mit
Worksheet_Change
Aber ob das sinnvoll ist, dass bei jeder �nderung das Kopieren
angestossen wird, bezweifle ich.
Claus Busch
und wenn du kopierst bevor du schlie�t oder speicherst, also nicht
gerade bei jeder Eingabe?
Also ein �berwachen von Workbook_BeforeClose oder Workbook_BeforeSave
Andreas Killer
>> Wann?
>> Soll direkt bei der Eingabe das Kopieren stattfinden
> Am liebsten, ja.
Dann kannst Du im Change-Ereignis der Tabelle "Produktliste" die
Eingaben �berwachen und direkt 1:1 in die Zieltabellen �bernehmen.
Der Code muss in das Codemodul der Tabelle, wie's geht steht hier:
http://www.online-excel.de/excel/singsel_vba.php?f=44#s4
Andreas.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim Tabelle As Variant, I As Integer
'Liste aller Zieltabellen
Tabelle = Array("Competitor Information", "Uploadliste")
'Ereignisse aus
Application.EnableEvents = False
'Durchlaufe jede ge�nderte Zelle einzeln
For Each R In Target
'Durchlaufe jede Zieltabelle
For I = LBound(Tabelle) To UBound(Tabelle)
'Schreibe den Wert in die gleiche Zelle dort
Sheets(Tabelle(I)).Range(R.Address) = R
Next
Next
'Ereignisse an
Application.EnableEvents = True
End Sub
Volker Neurath
> Hallo Volker,
>
> und wenn du kopierst bevor du schlieï¿œt oder speicherst, also nicht
> gerade bei jeder Eingabe?
> Also ein ï¿œberwachen von Workbook_BeforeClose oder Workbook_BeforeSave
Das geht nicht - aber wenn ich den Wechsel zwischen Worksheets ï¿œberwachen
kï¿œnnte, wï¿œre das eine Option.
Der Aussendienstler muss nï¿œmlich in eines der Worksheets, in das die Daten
kopiert werden, ebenfalls Eintragungen machen.
Kopiert werden sollen die Daten nur, um ihm doppelte bzw. dreifache Arbeit
zu ersparen.
Bei dem Zweiten Sheet ist der Grund fï¿œr das kopieren der, dass die Daten in
eine mit SAP verbundene webbasierte Applikation hochgeladen werden sollen -
diese sich aber an eventuell im Sheet vorhandenen Formeln stï¿œrt und dann
einfach abschmiert.
Wï¿œre dem nicht so, wï¿œrde ich, statt nach kopieren zu fragen, schlicht
Formeln wie
=Wenn(Istleer(Produktliste!A4);"";Produktliste!A4)
in den Zellen des Zielsheets benutzt haben - Eleganz hin oder her ;)
Volker Neurath
Claus Busch wrote:
> sorry, habe die Punkte vergessen. �ndere so ab:
> ..Range(.Cells(4, 1), .Cells(Ymax + 3, 2)).Copy _
> Destination:=Sheets("Competitor Information").Range("A3")
Das hat das problem wenigstens kurzfristig gel�st.
Ernsthaft: ich bin kurz davor, einfach
=wenn(istleer(quellzelle);"";quellzelle)
in die erste Zielzelle zu shcreiben und das herunterzuziehen bis
Sheetende.
Grund: dein Makro hat exkt *einmal* funktioniert.
Inhalt des Sheets gel�scht, wieder auf den button geklickt und - nichts
tut sich.
Was ist da los?!
Volker,
f�r den VBA unter Excel sich immer mehr als eine Welt des Grauens
offenbart.
Volker Neurath
kopiert diese Routine Formeln mit oder nicht?
Formeln im zu kopierenden Bereich sollen eben *nicht* mitkopiert werden,
nur Werte.
Volker
Claus Busch
Am Mon, 17 May 2010 10:11:58 +0200 schrieb Volker Neurath:
> Formeln im zu kopierenden Bereich sollen eben *nicht* mitkopiert werden,
> nur Werte.
probiers mal so in Workbook_SheetActivate. Damit werden dir beim
Aktivieren von "Competitor Information" die *Werte* aus Produktliste
kopiert.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Ymax As Long
'Letzte Zeile in Spalte A und B ermitteln
'und h�here Zeile bestimmen.
If ActiveSheet.Name = "Competitor Information" Then
ActiveSheet.Cells.Delete
With Sheets("Produktliste")
Ymax = WorksheetFunction.Max( _
.Range("A" & Rows.Count).End(xlUp).Row, _
.Range("B" & Rows.Count).End(xlUp).Row)
Debug.Print
'Alles von Zelle A4 bis B[letzte Zeile] kopieren
'nach Tabelle2
.Range(.Cells(4, 1), .Cells(Ymax, 2)).Copy
ActiveSheet.Range("A3").PasteSpecial xlPasteValues
End With
End If
End Sub
Volker Neurath
Claus Busch wrote:
> probiers mal so in Workbook_SheetActivate. Damit werden dir beim
> Aktivieren von "Competitor Information" die *Werte* aus Produktliste
> kopiert.
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> Dim Ymax As Long
> 'Letzte Zeile in Spalte A und B ermitteln
> 'und h�here Zeile bestimmen.
> If ActiveSheet.Name = "Competitor Information" Then
> ActiveSheet.Cells.Delete
> With Sheets("Produktliste")
> Ymax = WorksheetFunction.Max( _
> .Range("A" & Rows.Count).End(xlUp).Row, _
> .Range("B" & Rows.Count).End(xlUp).Row)
> Debug.Print
> 'Alles von Zelle A4 bis B[letzte Zeile] kopieren
> 'nach Tabelle2
> .Range(.Cells(4, 1), .Cells(Ymax, 2)).Copy
> ActiveSheet.Range("A3").PasteSpecial xlPasteValues
> End With
> End If
> End Sub
ich habe diesen code in das .activate Ereignis des "Competitor
Information" worksheets kopiert.
Funktionierte bestens, nachdem ich
ActiveSheet.Cells.Delete
auskommentiert hatte, da es mir die voreingestellten �beschriftenzeilen
l�scht ;)
ein ganz herzliches 'Danke' f�r deine hervorragende Hilfe.
Claus Busch
Am Mon, 17 May 2010 14:58:15 +0200 schrieb Volker Neurath:
> Funktionierte bestens, nachdem ich
>
> ActiveSheet.Cells.Delete
>
> auskommentiert hatte, da es mir die voreingestellten �beschriftenzeilen
> l�scht ;)
sch�n, dass es nun funktioniert.
Wenn du die Zellen nicht l�schst, darf aber die Tabelle in Produktliste
nicht kleiner werden. Falls das doch der Fall sein kann, m�sste man zum
L�schen den korrekten Range angeben.
Volker Neurath
> Wenn du die Zellen nicht l�schst, darf aber die Tabelle in Produktliste
> nicht kleiner werden. Falls das doch der Fall sein kann, m�sste man zum
> L�schen den korrekten Range angeben.
Klar. Daran versuche ich mich jetzt mal selber; ich glaube, das Prinzip
habe ich verstanden - wir werden sehen ;)
Ob das wirklich wichtig ist, werden wir ebenfalls sehen m�ssten;
_geplant_ habe ich, dass das Workbook als *.xlt Datei verteilt wird -
nat�rlich mit leeren Sheets, so dass dieser Fall eigentlich nicht
auftreten sollte.
Aber man weiss ja nie... ;)
Volker Neurath
Claus Busch wrote:
> sch�n, dass es nun funktioniert.
> Wenn du die Zellen nicht l�schst, darf aber die Tabelle in Produktliste
> nicht kleiner werden. Falls das doch der Fall sein kann, m�sste man zum
> L�schen den korrekten Range angeben.
Hab ich selber versucht - geht nicht.
was genau ist an folgendem Code falsch?
If ActiveSheet.Name = "Competitor Information" Then
'Bereich A3:Bn leeren
With Sheets("Competitor Information")
Ymax = WorksheetFunction.Max( _
.Range("A" & Rows.Count).End(xlUp).Row, _
.Range("B" & Rows.Count).End(xlUp).Row)
'Alles von Zelle A3 bis B[letzte Zeile] kopieren
'nach Tabelle2
.Range(.Cells(3, 1), .Cells(Ymax, 2)).Clear
End With
end if
Ergebnis eines durchlaufes ist: keine aktion, der Bereich wird nicht,
wie erwartet gel�scht, es passiert schlicht nichts, jedenfalls nichts
sichtbares
Volker Neurath
man sollte den Test auch so durchf�hren, dass man eine m�gliche
Ver�nderung erkennen kann *sch�m*
Naja, ist noch fr�h am Tag (und bis jetzt nicht meiner :( )
Claus Busch
nachfolgenden Code in das Codemodul von "Competitor Information":
Private Sub Worksheet_Activate()
Dim Ymax As Long
Application.ScreenUpdating = False
If ActiveSheet.Name = "Competitor Information" Then
'Die n�chste Zeile brauchst du nur, wenn die Tabelle
'in Produktliste auch kleiner werden kann
With ActiveSheet
Ymax = WorksheetFunction.Max( _
.Range("A" & Rows.Count).End(xlUp).Row, _
.Range("B" & Rows.Count).End(xlUp).Row)
.Range("A3:B" & Ymax).Clear
End With
With Sheets("Produktliste")
'Letzte Zeile in Spalte A und B ermitteln
'und h�here Zeile bestimmen.
Ymax = WorksheetFunction.Max( _
.Range("A" & Rows.Count).End(xlUp).Row, _
.Range("B" & Rows.Count).End(xlUp).Row)
Debug.Print
'Alles von Zelle A4 bis B[letzte Zeile] kopieren
'nach Tabelle2
.Range(.Cells(4, 1), .Cells(Ymax, 2)).Copy
ActiveSheet.Range("A3").PasteSpecial xlPasteValues
End With
End If
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
Volker Neurath
[code gesnipped]
Dnke dir - auch wenn ich das Problem selber lï¿œsen konnte.