Makros: Tabellenblatt kopieren, Spalte kopieren

PremiumDC · 1. September 2008 um 17:05

Hallo zusammen,
ich versuche ein Makro zu schreiben, welches folgende Funktionen erfüllen soll:

abfragen, in welcher Spalte die zur Zeit aktivierte Zelle liegt und diesen Wert speichern
gesamtes Tabellenblatt markieren
neues Dokument öffnen
in neues Dokument einfügen: - Werte - Formate
jetzt zurück in das ursprungsdokument und die entsprechende Spalte kopieren und in das neue Dokument einfügen

Ziel ist es also ein neues Dokument zu erstellen, welches bis auf die gewünschte Spalte frei von Formeln ist.

Mein bisheriges Makro kann alles bis auf die Spalte kopieren und einfügen. Der Code lautet wie folgt:

Sub Tabellenblattkopieren()
Dim myFileName As String, mySavePfad As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.Copy
Cells.Select
Cells.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Neben diesem Problem habe ich noch zwei weitere(dämliche)Fragen zu Makros:
Ist es möglich ein Makro unabhängig von einem Dokument (quasi global) zu hinterlegen? Ich bearbeite grundsätzlich Dateien, welche ich nicht selbst erstellt habe (in denen das Makro also nicht gespeichert wurde). Nachdem ich das z.B. das oben beschriebene Makro einem Knopf zugeordnet habe und diesen in einem anderen Dokument benutzt habe geschah dies immer dadurch, dass das „alte“ Dokument geöffnet wurde.

Ist es ausserdem möglich das Makro auf verschiedenen Arbeitsplätzen zu nutzen, ohne dies jeweil seperat einzurichten?

Vielen Dank für eure Hilfe!!!
MFG

Reinhard_fb8cb7 · 1. September 2008 um 18:04

zu 2/3 und 3/3
Hi Ingo,

Ist es möglich ein Makro unabhängig von einem Dokument (quasi
global) zu hinterlegen?

ja, in deiner personl.xls oder in einem Add-In (xla) die du durch den Add-In Mangaer in Excel einbindest.

Ich bearbeite grundsätzlich Dateien,
welche ich nicht selbst erstellt habe (in denen das Makro also
nicht gespeichert wurde). Nachdem ich das z.B. das oben
beschriebene Makro einem Knopf zugeordnet habe und diesen in
einem anderen Dokument benutzt habe geschah dies immer
dadurch, dass das „alte“ Dokument geöffnet wurde.

Mir unklar was du da meinst.

Ist es ausserdem möglich das Makro auf verschiedenen
Arbeitsplätzen zu nutzen, ohne dies jeweil seperat
einzurichten?

Ich glaube nein, beim Start von Excel hat es die Makros zur Verfügung die in Personl.xls oder in xlas stehen.
Von daher müßte man die entsprechechende Personl.xlx auf seinem Rechner haben oder die xlas eingebunden haben.

Wie das nun in einem Netzwerk aussieht, wo Excel nicht auf deinem Rechner liegt sondern zentral für alle, weiß ich nicht.

Gruß
Reinhard

PremiumDC · 2. September 2008 um 09:07

Hallo Reinhard,
vielen Dank für die Antwort. Hab wieder etwas dazu gelernt

Nachdem ich das z.B. das oben beschriebene Makro einem
Knopf zugeordnet habe und diesen in einem anderen Dokument benutzt
habe geschah dies immer dadurch, dass das „alte“ Dokument geöffnet
wurde.

Mir unklar was du da meinst.

Damit wollte ich ausdrücken, dass wenn ich versuche das Makro, welches ich in Dokument xyz geschrieben habe aus einem anderen zu starten, dies immer dadurch geschah, dass das xyz geöffnet wurde.
Aber ich denke dies dürfte dann nicht mehr passieren, wenn ich es wie von dir geschrieben einbinde. Werde das gleich mal versuchen.

Kannst du mir auchnoch mit meinem ursprünglichen Problem weiter helfen?
Ich versuche ein Dokument zu dublizieren, wobei jedoch in allen bis auf einer Spalte die Formeln gelöscht werden soll.
Grundsätzlich fallen mir da zwei Wege ein:

Alles kopieren, neues Dokument, Inhalte einfügen: Werte + Formate, Zurück ins Ursprungsdokument, „Aktive Spalte“ kopieren, in neues Dokument einfügen
Links von „Aktive Spalte“ Spalten kopieren, Inhalte einfügen: Werte+Formate, Rechts von „Aktive Spalte“ Spalten kopieren, Inhalte einfügen: Werte+Formate, Aktive Spalte kopieren und komplett einfügen

Würde mich sehr über Hilfe freuen,

beste Grüße

Ingo

Reinhard_fb8cb7 · 2. September 2008 um 11:41

Makro in Add-In ausführen per Menusymbol

Aber ich denke dies dürfte dann nicht mehr passieren, wenn ich
es wie von dir geschrieben einbinde. Werde das gleich mal
versuchen.

Kannst du mir auchnoch mit meinem ursprünglichen Problem
weiter helfen?
Ich versuche ein Dokument zu dublizieren, wobei jedoch in
allen bis auf einer Spalte die Formeln gelöscht werden soll.

Hallo Ingo,

nimm ein neue leere Arbeitsmappe, Alt+F11, Einfügen–Modul, dort den nachstehenden Code der da rein soll reinkopieren.

Dann Doppelklick auf „DieseArbeitsmappe“, die beiden Prozeduren die da rein sollen, reinkopieren, Editor schließen.

Dann die Mappe als Add-in (xla) speichern bei „Speichern unter“.

Dann das Add-In mit Extras–Add-iNmanager einbinden.

Nun müßte oben ein neues Symbol in der Menuleiste existieren. Dies ruft dann dein Makro auf.

Anderen Presonen mzußt du dann nur die xla zuschicken (wenn sie funktioniert *gg*) und sie müßen sie dann auch nur einbinden.
Braucht man es nicht mehr so geht das auch wieder über den Add-In-Manager.

Gruß
Reinhard

in Standardmodul, z.B. Modul1

Option Explicit
'
Sub Tabellenblattkopieren()
Dim Spa As Long, Mldg As String, Eing
On Error GoTo Ende
Application.ScreenUpdating = False
Mldg = "Alle Zellformeln werden durch Werte ersetzt" & Chr(13) & Chr(13)
Mldg = Mldg & "außer in der Spalte " & Spalte(activecell.Column) & Chr(13) & Chr(13)
Mldg = Mldg & "Wollen Sie fohrtfahren?"
Eing = MsgBox(Mldg, vbYesNo, "Sicherheitsabfrage")
If Eing vbYes Then GoTo Ende
For Spa = 1 To ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
 If Spa activecell.Column Then
 Columns(Spa).Copy
 Columns(Spa).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= \_
 False, Transpose:=False
 End If
Next Spa
Application.CutCopyMode = False
Ende:
Application.ScreenUpdating = True
If Err.Number 0 Then MsgBox "Ein Fehler trat auf, Ingo kontaktieren."
End Sub
'
Function Spalte(ByVal Spa As Integer) As String
Dim N As Byte, Adr As String
Adr = Cells(1, Spa).Address
For N = 2 To InStr(2, Adr, "$") - 1
 Spalte = Spalte & Mid(Adr, N, 1)
Next N
End Function
'
Sub SymbolErzeugen()
Dim cmbb As CommandBarButton
Call SymbolLoeschen
Set cmbb = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, temporary:=True)
With cmbb
 .Caption = "Spalten"
 .FaceId = 612
 .OnAction = ThisWorkbook.Name & "!Tabellenblattkopieren"
End With
Set cmbb = Nothing
End Sub
'
Sub SymbolLoeschen()
Dim C As CommandBarControl
For Each C In Application.CommandBars("Worksheet Menu Bar").Controls
 If C.Caption = "Spalten" Then C.Delete
Next C
End Sub

In DieseArbeitsmappe:

Option Explicit
’
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call SymbolLoeschen
End Sub
’
Private Sub Workbook_Open()
Call SymbolErzeugen
End Sub

PremiumDC · 2. September 2008 um 16:49

Hallo Reinhard,
zunächst einmal vielen vielen Dank für die umfangreiche Programmierung.
Leider funktioniert das Makro nicht. Die Schleife scheint fast immer nach dem ersten Durchlauf (also nach der ersten Spalte) aufzuhören. Gelegentlich ist nach dem Durchlauf auch eine andere Spalte (aber immer links von der „ausgewählten Spalte“) markiert. Gründe hierfür z.B. in der Struktur der jeweiligen Datei oder des Formats der Zellen kann ich leider nicht erkennen.
Auch eine Fehlermeldung ist nicht aufgetaucht. [Ist der Ausdruck:
„If Err.Number 0“ korrekt?]
Ich kann zwar fast vollständig nachvollziehen, was das Programm bisher macht, meine Versuche selbst den Fehler zu finden sind jedoch kläglich gescheitert.
Ich würde mich sehr freuen, wenn du das noch „reparieren“ könntest.
Dank und Gruss,
Ingo

Reinhard_fb8cb7 · 2. September 2008 um 17:17

Hallo Ingo,

Leider funktioniert das Makro nicht.

*hmmh*, ich habe es in einer Tabelle mit paar Formeln getestet, da lief es.

markiert. Gründe hierfür z.B. in der Struktur der jeweiligen
Datei oder des Formats der Zellen kann ich leider nicht
erkennen.

Bastle mal bitte eine Beispieltabelle wo der Fehler auftritt und lade sie hoch bei FAQ:2861 o.ä. und poste hier den Link.

Auch eine Fehlermeldung ist nicht aufgetaucht. [Ist der
Ausdruck:
„If Err.Number 0“ korrekt?]

Ja, wenn der Code fehlerfrei durchläuft ist die Fehlernummer Null, ansonsten ungleich Null und es müßte die MsgBox kommen.

Ich würde mich sehr freuen, wenn du das noch „reparieren“
könntest.

Wie gesgt, bei mir lief das, wenn es bei dir nicht läuft kann das an vielem liegen, auch wenn da manches hier nicht zutreffen sollte, es könnte an dm liegen der auf dem Stuhl vor dem PC sitzt ), oder es sind da verbundene Zellen, aktiver Blattschutz, oder oder störend.

Ohne Datenschutzgründen wäre es für mich am Besten ich kriege die Originaldatei. Andrerseits habe ich auch Null Bock mir mal so eine 10 MB Datei mal anzuschauen.
Also eine Beispielmappe ist angesagt, klein fein und der Fehler tritt auf, so daß ich den Fehler nachvollziehen kann.

Gruß
Reinhard

Marion_L_we · 3. September 2008 um 01:51

Hallo Reinhard,

Wie das nun in einem Netzwerk aussieht, wo Excel nicht auf
deinem Rechner liegt sondern zentral für alle, weiß ich nicht.

auch im Netzwerk hat jeder Arbeitsplatzrechner sein eigenes Excel direkt auf dem Rechner installiert
updates werden zentral meist beim booten verteilt und installiert, genauso sonstige software, persönliche Einstellungen in den Programmen werden dabei nicht verändert, auch nicht Dateien, AddIns, …
Lediglich wenn einer Probleme mit der Anwendersoftware hat, ist meist eine Neuinstallation des betreffenden Programms mit dem geringsten Aufwand verbundenn. Das passiert aber nur auf Anforderung durch den User. Dann allerdings hat man anschließend ein neu installiertes Excel.

Natürlich kann man AddIns auch auf einem share speichern, jeder user hat normalerweise ein persönliches (log.) „Laufwerk“ auf einem zentralen öffentlichen Laufwerk, das auch regelmäßig mitgesichert wird. Dann kann man von jedem beliebigen Rechner im Netzwerk mit ein paar Klicks seine persönlichen Dateien, Makros, AddIns usw. an dem jeweiligen Arbeitsplatz nutzen oder auch mit anderen teilen.

Gruß
Marion

Reinhard_fb8cb7 · 3. September 2008 um 09:05

Hallo Marion,

Wie das nun in einem Netzwerk aussieht, wo Excel nicht auf
deinem Rechner liegt sondern zentral für alle, weiß ich nicht.

auch im Netzwerk hat jeder Arbeitsplatzrechner sein eigenes
Excel direkt auf dem Rechner installiert

aha, die Zeiten von Terminals die nur aus Tastatur, Maus und Bildschirm bestehen ist wohl vorbei in den meisten Netzwerken.

updates werden zentral meist beim booten verteilt und
installiert, genauso sonstige software, persönliche
Einstellungen in den Programmen werden dabei nicht verändert,
auch nicht Dateien, AddIns, …

Natürlich kann man AddIns auch auf einem share speichern,
jeder user hat normalerweise ein persönliches (log.)
„Laufwerk“ auf einem zentralen öffentlichen Laufwerk, das auch
regelmäßig mitgesichert wird. Dann kann man von jedem
beliebigen Rechner im Netzwerk mit ein paar Klicks seine
persönlichen Dateien, Makros, AddIns usw. an dem jeweiligen
Arbeitsplatz nutzen oder auch mit anderen teilen.

Danke dir für die Informationen.

Lg
Reinhard

PremiumDC · 3. September 2008 um 09:51

Hallo Reinhard,
hier ist eine Beispieldatei:
http://www.hostarea.de/server-09/September-44555e246…
Die Tatsache, dass es bei mir dort nicht funktioniert läßt mich darauf schliessen, dass es ein Fehler bei der Einbindung gibt. Ich habe mich jedoch strikt an die Anweisungen gehalten. Das Symbol erscheint oben in der Zeile, die Fortfahren Ja/Nein Abfrage funktioniert und dann endet es.
Wenn ich das Programm zur Zeit richtig verstehe, so wird Spalte für Spalte vorgegangen und jede kopiert die markierte Spalte noch nicht erreicht ist. Eventuell könnte man in einem Schritt gleich alle Spalten links von der ausgewählten und alle Spalten rechts von der ausgewählten markieren und (-Formeln) kopieren.
Inwiefern wäre es möglich bei jetziger Formel eine neue Datei zu erzeugen, in welche das Dokument kopiert wird.
Meine ursprüngliche Idee war ja gewesen, das gesamte Dokument zu kopieren, ein neues zu erstellen, alles dort (-Formeln) einzufügen, zurückzugehen, die aktive Spalte zu kopieren und an der Stelle im neuen Dokument einzufügen. Ich bin nur leider daran gescheitert die Dokument korrekt anzusprechen.
Ich danke dir sehr für deine Hilfe und Geduld!
Beste Grüße,
Ingo

Reinhard_fb8cb7 · 3. September 2008 um 10:40

Hallo Ingo,

http://www.hostarea.de/server-09/September-44555e246…

Die Tatsache, dass es bei mir dort nicht funktioniert läßt
mich darauf schliessen, dass es ein Fehler bei der Einbindung
gibt.

nein, bzw. Einbindungsfehler kann ich ja nicht sehen da ich deine xla nicht habe.
Das Problem liegt in dieser Zeile:

For Spa = 1 To ActiveSheet.Cells(1,Columns.Count).End(xlToLeft).Column

an der 1 in „Cells(1,Columns.Count)“, sie gibt die Zeile an in der Vba nachschauen soll wieviele Spalten eigentlich benutzt sind.
Man könnte das um schreiben ode du gibst eine andere Zeilennummer an, aber viel einfacher ist, du wirst ja wissen wieviele Spalten es gibt, trage dort einfach die maximale Anzahl ein und ersetzte dann die Codezeile durch:

For Spa = 1 To 15

wenn es max. 15 Spalten gibt. Aber ist nicht soooo wichtig, kannst auch 256 hinschreiben, klappt auch, dauert halt länger.

Ein davon unabhängiger Codefehler fiel mir noch auf, habe dementsprechend den Code geändert, der neue steht nachstehend.
Er ist getestet.

Gruß
Reinhard

Sub Tabellenblattkopieren()
Dim Spa As Long, Mldg As String, Eing, AC As Long
On Error GoTo Ende
Application.ScreenUpdating = False
AC = ActiveCell.Column
Mldg = "Alle Zellformeln werden durch Werte ersetzt" & Chr(13) & Chr(13)
Mldg = Mldg & "außer in der Spalte " & Spalte(AC) & Chr(13) & Chr(13)
Mldg = Mldg & "Wollen Sie fohrtfahren?"
Eing = MsgBox(Mldg, vbYesNo, "Sicherheitsabfrage")
If Eing vbYes Then GoTo Ende
For Spa = 1 To 15
 If Spa AC Then
 Columns(Spa).Copy
 Columns(Spa).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= \_
 False, Transpose:=False
 End If
Next Spa
Application.CutCopyMode = False
Ende:
Application.ScreenUpdating = True
Range("A1").Select
If Err.Number 0 Then MsgBox "Ein Fehler trat auf, Ingo kontaktieren."
End Sub

PremiumDC · 3. September 2008 um 14:46

1000 Dank
Hallo Reinhard,
vielen Dank für die Hilfe! Es funktioniert jetzt alles so wie ich es mir vorgestellt habe.
Beste Grüße,
Ingo