PDA

Vollständige Version anzeigen : Vermeidung von Select und Activate und ActiveSheet


Julia S.
14.09.2011, 00:02
Guten Abend,

ich möchte gerne generell meine Codes optimieren und scheitere dabei leider kläglich. Ich habe die unten aufgeführten, auskommentierten Schritte durchgeführt und bin auf keinen grünen Zweig gekommen.

Ich möchte gerne im Vorfeld einer Sub sowohl die Tabellenblätter als auch den Bereich vordefinieren und dann in einem Copy-Schritt die Daten einer Reihe in ein anderes Blatt transponieren.

Mir ist unter anderem aufgefallen, dass ich, wenn ich mit den vordefinierten Tabellenarbeite, eine Range mithilfe von Address-Verweisen (also Range(Cells(1,1).Address, Cells(8,1).Address) aufrufen muss, wo sich für mich auch kein Sinn ergibt.

Vielen Dank für Ratschläge,
Julia

Sub KopierenUndTransponieren()
Application.ScreenUpdating = False

Dim wStart As Worksheet 'Worksheet für das Auswertung
Dim wZiel As Worksheet 'Worksheet für die Hilfstabelle
Dim iLetzteZeile As Integer 'Integer für die letzte Zeile

Set wStart = Worksheets("Sheet1")
Set wZiel = Worksheets("Sheet2")

iLetzteZeile = _
wStart.Cells.Find(What:="*", after:=Cells(1, 1), SearchDirection:=xlPrevious).Row

Set rBereich = Range(Cells(2, 1), Cells(iLetzteZeile, 1))

'FUNKTION OK
'Debug.Print ActiveWorkbook.Worksheets("Sheet1").Range("A1:A8").Address
'Debug.Print rBereich.Address
'Debug.Print wStart.Name
'Debug.Print wStart.Range("A1").Value
'Debug.Print wStart.Range("A1").Address
'Debug.Print wStart.Range(Cells(1, 1).Address, Cells(8, 1).Address).Address
'Debug.Print wStart.Cells(1, 1).Address
'Debug.Print ActiveSheet.Range(Cells(2, 3), Cells(10, 4)).Address
'Debug.Print ActiveWorkbook.ActiveSheet.Range(Cells(2, 3), Cells(10, 4)).Address
'Debug.Print Sheets("Sheet1").Range("A1").Value
'Debug.Print Sheets("Sheet1").Range(Cells(1, 1).Address).Value
'ActiveSheet.Range("A2:A8").Select

'IN DER ENTWICKLUNG
'wStart.rBereich.Copy
'wZiel.Range(Cells(1,1).Address).PasteSpecial _
'Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
'Transpose:=True


'KEINE FUNKTION
'Debug.Print ActiveWorkbook.wStart.Range("A1:A8").Address
'Debug.Print ActiveWorkbook.Worksheets("Sheet1").Range(Cells(1, 1), Cells(8, 1)).Address
'Debug.Print ActiveWorkbook.wStart.rBereich.Address


Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

CitizenX
14.09.2011, 01:31
Hi,

Mir ist unter anderem aufgefallen, dass ich, wenn ich mit den vordefinierten Tabellenarbeite, eine Range mithilfe von Address-Verweisen (also Range(Cells(1,1).Address, Cells(8,1).Address) aufrufen muss, wo sich für mich auch kein Sinn ergibt.
Wenn der Bezug nicht auf das aktive Blatt hergestellt werden soll, musst du Ihn auch auf die Zelle setzen (wStart.Range(wStart.Cells(2, 1), wStart.Cells(iLetzteZeile, 1))
..oder du arbeitest mit der With Anweisung:

With Sheets("Tabelle1")
.Range(.Cells(1,1),.Cells(5,1))
End With
Aber Obacht: immer schön auf die Punktsetzung achten ;-)



Sub KopierenUndTransponieren()
Dim wStart As Worksheet 'Worksheet für das Auswertung
Dim wZiel As Worksheet 'Worksheet für die Hilfstabelle
Dim iLetzteZeile As Long 'letzte Zeile der Auswertungstabelle
Dim rBereich As Range 'Kopierbereich
Dim iCalc 'Zustand der Berechnung

Set wStart = Sheets("Tabelle1")
Set wZiel = Sheets("Tabelle2")

iLetzteZeile = wStart.Cells(wStart.Rows.Count, 1).End(xlUp).Row
Set rBereich = wStart.Range(wStart.Cells(2, 1), wStart.Cells(iLetzteZeile, 1))

On Error GoTo ErrH

With Application
.ScreenUpdating = False
.EnableEvents = False
iCalc = .Calculation
.Calculation = xlCalculationManual
End With

rBereich.Copy
wZiel.Range(wZiel.Cells(1, 1).Address).PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

ErrH:
With Application
.CutCopyMode = False
.EnableEvents = True
.Calculation = iCalc
.ScreenUpdating = True
End With

Set wStart = Nothing
Set wZiel = Nothing
Set rBereich = Nothing

End Sub

Pit987
14.09.2011, 04:55
Hallo!
Code optimieren?? Ist das Makro zu langsam?
Schneller wird der Code, wenn Du von Select ganz weggehst, Array Berechnungen nur im Speicher erledigst etc. Dann allerdings um den Faktor 10 oder mehr.
ScreenUpdating ein- oder ausschalten kostet ebenfalls Zeit. Beruhigt aber die Augen (-:
Beim Transponieren bin ich der Meinung, dass Quell- und Zielbereich gleich groß sein müssen. Wenn ich richtig gelesen habe, steht bei Dir aber A1:A8 und A2:A8.

Julia S.
14.09.2011, 07:02
Guten Morgen,

jetzt weiss ich auch, warum so oft mit With-Anweisungen gearbeitet wird. Mir ist ein kleines Licht aufgegangen :-).

"Code optimieren?? Ist das Makro zu langsam?"
Ja, der ursprüngliche Code sieht aus wie Kraut und Rüben und umfasst mehr Subs. Daher versuche ich gerade Stück für Stück das 'gute' Coden zu lernen.

"Beim Transponieren bin ich der Meinung, dass Quell- und Zielbereich gleich groß sein müssen."

Das stand da noch drin, weil ich schon vorher debuggen musste. Am Ende werde ich im Zielbereich nur die erste Zelle ansteuern, denke ich zumindest.

Vielen Dank für die Ratschläge,
Gruß,
Julia

Julia S.
14.09.2011, 07:32
Moin,

ich versuche das direkt umzusetzen, aber momentan gelingt mir die Syntax nicht. Ich erhalte einen Syntaxfehler für die Zeile, die mit Paste:=xlPasteValues anfängt.

With wStart
iLetzteZeile = _
.Cells.Find(What:="*", after:=.Cells(1, 1), SearchDirection:=xlPrevious).Row

Debug.Print .Range(.Cells(1, 1), .Cells(iLetzteZeile, 1)).Address
Debug.Print wZiel.Cells(1, 1).Address

.Range(.Cells(1,1), .Cells(iLetzteZeile, 1)).Copy _
wZiel.Cells(1, 1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, _
'Transpose:=True

End With

hary
14.09.2011, 08:38
Hallo
Ungetestet.
es liegt wahrscheinlich an dieser Zeile:
1)).Copy wZiel.Cells(1, 1).PasteSpecial

Beim normalen Copy kannst Du es in eine Zeile bringen. Bei PasteSpecial muss es in zwei Codezeilen.
Bsp. bei Copy eine Codezeile:
Bereich.copy Bereich

bei PasteSpecial zwei Codezeilen:
Bereich.copy
Bereich

gruss hary

Julia S.
14.09.2011, 09:23
Ah, danke! Das hatte ein bisschen etwas von 'War der Stromstecker eingesteckt?'.

Lieben Gruß,
Julia

Julia S.
14.09.2011, 09:50
Und direkt noch eine Frage; ich hoffe, ich fange nicht an zu nerven :-)

Die Zielsetzung ist ja, ohne Select und Activate zu arbeiten.

Klappt das auch, wenn ich ein neues Tabellenblatt erstellen und benennen will?

Bisher hatte ich:
Sheets("Sheet3").Select
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = sNeuesProdukt
wStart.Select

Prinzipiell kann ich ja auch folgendes machen:
wKopie.Copy after:=wKopie

Aber wie benenne ich das dann? Das neue Tabellenblatt heißt ja immer anders. Gibt es eine Definiton für das zuletzt erstellte Tabellenblatt?

Gruß,
Julia

Beverly
14.09.2011, 09:59
Hi Julia,

wenn du ein Tabellenblatt kopierst, ist die Kope immer das aktive Tabellenblatt, sodass du dich bei der Namensgebung darauf beziehen kannst:
Sheets("Sheet3").Copy after:=Sheets("Hallo")
ActiveSheet.Name = sNeuesProdukt
<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/" onclick="window.open(this.href);return false"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

Pit987
14.09.2011, 10:09
Wenn Du mit einem Makro ein neues Tabellenblatt erstellen willst, heißt es zwar immer anders, wird doch aber bestimmt irgendwelchen Regeln folgen.

Ich würde in eine Zelle auf dem bestehenden Sheet den aktuellen Namen anzeigen und dann in einer weiteren Zelle den neuen geplanten Namen eintragen.

Eine Variable, die dann immer den weiterentwickelten Blattnamen enthält, als Input benutzen.
So mache ich das mit Dateien. Ein einzelnes Blatt benennen würde ich umgehen, indem ich für einen Monat oder ein Jahr die Blattnamen selbst vergebe.

Pit987
14.09.2011, 10:16
Kannst Du die gültigen Blattnamen in eine Spalte schreiben, daneben dann Tabelle1 bis Tabelle99 schreiben und per SVERWEIS dann mit dem gesuchten Namen auf das entsprechende Tabellenblatt wechseln?

Beim Dateinamen/Blattnamen auslesen muß man glaube ich erst speichern, ehe man eine Info bekommt.

Pit987
14.09.2011, 10:45
Hier der Code für Blattnamen

Julia S.
14.09.2011, 10:48
Ah, dann werde ich wohl doch einmal Select verwenden müssen, um am Ende der Funktion zurück auf das Anfangstabellenblatt zu gelangen.

Herzlichen Dank an alle :-),
lieben Gruß,
Julia