Formate übertragen auf mehrere Blätter each

Computer: Software & Programmierung Office Software
1

Hallo,
da bin ich wieder mit weiteren Problemen:
Nach einigen Versuchen frage ich dann Euch, warum die Referenzierung so nicht klappt ( unterstes Makro ). Die oberen Makros laufen bestens.
Ich freue mich über jede Antwort :smile:

Option Explicit
Dim zeile As Integer
Dim spalte As Integer
Dim blatt As Worksheet
Sub alle_sperren()
For Each blatt In Worksheets
blatt.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
Next blatt
End Sub
Sub alle_entsperren()
For Each blatt In Worksheets
blatt.Unprotect
Next blatt
End Sub
Sub alle_formeln()
Call aeef
For Each blatt In Worksheets
If blatt.Name „Eingabe“ And blatt.Name „Liste“ Then
blatt.Range(„A1:M17“).FormulaR1C1 = „=Eingabe!RC“
blatt.Range(„B1:B6“).ClearContents
blatt.Range(„C6:G6“).ClearContents
blatt.Range(„c1“).ClearContents
blatt.Range(„B9:C15“).Locked = False
blatt.Range(„I4:I8“).Locked = False
blatt.Range(„I6“).Locked = True
blatt.Range(„I10“).Locked = False
blatt.Range(„J13:J15“).Locked = False
blatt.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
End If
Next blatt
Call aeet
End Sub
'-------------------hier gehts los -------
Sub Formate()
Call aeef
For Each blatt In Worksheets
If blatt.Name „Eingabe“ And blatt.Name „Liste“ Then 'hier funktioniert „blatt.name“ einwandfrei

'Variante 4:
’ blatt.Name.Range(Cells(1, 1), Cells(16, 20)).MergeCells = False '*Fehler in dieser Zeile mit Markierung auf „blatt.name“
’ For zeile = 1 To 16
’ For spalte = 1 To 20
’ Sheets(„Eingabe“).Cells(zeile, spalte).Copy blatt.Cells(zeile, spalte)
’ Next spalte
’ Next zeile

'Variante 3:
’ blatt.Range(Cells(1, 1), Cells(16, 20)).MergeCells = False '*Fehler in dieser Zeile
'Die obige Zeile eingefügt, nachdem Probleme mit verbundenen Zellen gemeldet wurden
’ For zeile = 1 To 16
’ For spalte = 1 To 20
’ Sheets(„Eingabe“).Cells(zeile, spalte).Copy blatt.Cells(zeile, spalte)
’ Next spalte
’ Next zeile

'Variante 2:
'Sheets(„Eingabe“).Cells.Copy blatt.Cells 'alle SchaltFlächen und Graphik ( Logo ) werden mitkopiert. Formeln sind kein Problem, werden später wieder eingefügt

'Variante 1: --> anwendungs- oder objektdefinierter Fehler (1004)
Sheets(„Eingabe“).Range(Cells(1, 1), Cells(16, 20)).Copy blatt.Range(Cells(1, 1), Cells(16, 20)) ’

With blatt.Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
blatt.Range(„A1:M17“).FormulaR1C1 = „=Eingabe!RC“
blatt.Range(„B1:B6“).ClearContents
blatt.Range(„C6:G6“).ClearContents
blatt.Range(„c1“).ClearContents
blatt.Range(„B9:C15“).Locked = False
blatt.Range(„I4:I8“).Locked = False
blatt.Range(„I6“).Locked = True
blatt.Range(„I10“).Locked = False
blatt.Range(„J13:J15“).Locked = False
blatt.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
End If
Next blatt
Call aeet
End Sub

2

Nach einigen Versuchen frage ich dann Euch, warum die
Referenzierung so nicht klappt ( unterstes Makro ). Die oberen
Makros laufen bestens.

Hallo Thomas,

bitte mache die Zeilen nicht so lang :frowning: Ich sehe nur 95 Zeichen.
Unübersichtlich immer nach rechts zu crollen.

.Range(cells(…),cells(…))
mußt du Cells auch referenzieren:
.Range(.cells(…),.cells(…))

Bei .Copy destination:= reicht bei Destination die linke obere Zelladrsse

With Worksheets("Eingabe")
 For Each blatt In Worksheets
 If blatt.Name "Eingabe" And blatt.Name "Liste" Then
 .Range(.Cells(1, 1), .Cells(16, 20)).Copy blatt.Cells(1, 1)

oder:

For Each blatt In Worksheets
 If blatt.Name "Eingabe" And blatt.Name "Liste" Then
 Worksheets("Eingabe").Cells(1, 1).Resize(16, 20).Copy blatt.Cells(1, 1)

Gruß
Reinhard

3

Hallo Reinhard,
vielen Dank für Deine Antwort. Ich werde es gleich testen und gehe
davon aus, dass es dann klappt :smile:

bitte mache die Zeilen nicht so lang :frowning: Ich sehe nur 95
Zeichen.
Unübersichtlich immer nach rechts zu scrollen.

Sorry. Ich werde es in Zukunft berücksichtigen.
Vom Forum her etwas ungünstig, dass im Editor umgebrochen wird und dann im Beitrag nicht …
Die Vorschau hilft da auch nicht weiter. Musste noch einmal löschen und neu schreiben mit Enter.

.Range(cells(…),cells(…))

Bei .Copy destination:= reicht bei Destination die linke obere
Zelladresse

klar

Dass die Range-Grenzen auch vollständig referenziert werden müssen, werde ich versuchen mir zu merken.
Gedanklich ein wenig gewöhnungsbedürftig :-S
Schließlich kann mann doch ein Range nicht über mehrere Blätter abgrenzen, oder?
Wie sähe das dann in A1-Schreibweise aus? ( Nur interessehalber … )

Nun hast Du die andere Anfrage von mir im Prinzip gleich mit erledigt.
Mit Deinen Änderungen sollte es funktionieren ( allerdings nicht so elegant mit den vielen Blättern … )
Vielen Dank noch einmal und schönes WE
Thomas

4

Hallo Thomas,

bitte mache die Zeilen nicht so lang :frowning: Ich sehe nur 95
Zeichen.

Sorry. Ich werde es in Zukunft berücksichtigen.
Vom Forum her etwas ungünstig, dass im Editor umgebrochen wird
und dann im Beitrag nicht …

? ich meinte in deinem Vba-Editor.
Klar, ist dein Rechner und wenn du „Falken“-Augen :smile: hast kannste
ja Schriftgröße 4 einstellen usw. dann liest du bei dir auch
250 Zeichen pro Zeile ohne nach rechts zu scrollen.

Bei .Copy destination:= reicht bei Destination die linke obere
Zelladresse

klar

Sorry Sir *gg*, war nicht so ersichtlich.

Schließlich kann man doch ein Range nicht über mehrere
Blätter abgrenzen, oder?

Höchstwahrscheinlich nicht. Müßte man testen um nahe 100% nein sagen zu können.
Eine Range aus mehreren Zellen hat eine Summe usw. aber keinen Wert.
Aber man kann dem Gesamt-Range einen Wert zuweisen. Den haben dann alle Zellen dort.

Nun kann man ja auch mehrere Blätter vereinen, auf einem einen Zellbereich amrkieren und dem einen Wert zuweisen. Dann entspricht ja
der Gesamt-range dem was du anfragst.

Wie sähe das dann in A1-Schreibweise aus? ( Nur
interessehalber … )

Da gelten die gleichen Regeln.

Mit Deinen Änderungen sollte es funktionieren ( allerdings
nicht so elegant mit den vielen Blättern … )

Zeig mal den entstandenen Code, vielleicht kann man den aufhübschen.
Wenn sich unelegant auf die Blätter bezieht vielleicht eine Beispielmappe.

Gruß
Reinhard

5

Hallo Reinhard,
die Mappe habe ich hier in einigermaßen anonymisierter Form:
http://www.file-upload.net/download-4408835/TypenSch…
…

? ich meinte in deinem Vba-Editor.
Klar, ist dein Rechner und wenn du „Falken“-Augen :smile: hast

naja, das war schon ´mal besser :-S

kannste ja Schriftgröße 4 einstellen usw. dann liest du bei dir auch
250 Zeichen pro Zeile ohne nach rechts zu scrollen.

LOL
Liegt wohl daran, dass ich mich in meinen eigenen aktuellen Makros selten für die rechten Enden der Zeilen interessiere. Bei FremdCode ist das natürlich anders.

…

Schließlich kann man doch ein Range nicht über mehrere
Blätter abgrenzen, oder?

Höchstwahrscheinlich nicht. Müßte man testen um nahe 100% nein
sagen zu können.
Eine Range aus mehreren Zellen hat eine Summe usw. aber keinen
Wert.
Aber man kann dem Gesamt-Range einen Wert zuweisen. Den haben
dann alle Zellen dort.

Ich hoffe ´mal, dass ich das niemals brauchen werde. Aber vielleicht hilft mir diese Überlegung, mir zu merken, dass man die Range-Grenzen auch referenzieren muss :wink:

Nun kann man ja auch mehrere Blätter vereinen, auf einem einen
Zellbereich markieren und dem einen Wert zuweisen. Dann
entspricht ja der Gesamt-range dem was du anfragst.

Ich hoffe ´mal, dass ich das niemals brauchen werde.
…

Zeig mal den entstandenen Code, vielleicht kann man den
aufhübschen.
Wenn sich unelegant auf die Blätter bezieht vielleicht eine
Beispielmappe.

Das kann man sicherlich. Das grundsätzliche Problem sind wohl eher die vielen fast gleichen Blätter, die ich aufwändig in den GleichSchritt zwingen muss. Sieht halt etwas übertrieben aus - wie mit Kanonen auf Spatzen zu schießen :-S

Gruß
Reinhard

Vielen Dank für Deine Hilfe und Dein Interesse.
Schönes WE
Thomas

6

Hallo Thomas,

die Mappe habe ich hier in einigermaßen anonymisierter Form:
http://www.file-upload.net/download-4408835/TypenSch…

„einigermaßen“?, sehr schön, vllt. ist ja noch was intimgeheimes drin
was meine Neugierde erfreut :smile:

kannste ja Schriftgröße 4 einstellen usw. dann liest du bei dir auch
250 Zeichen pro Zeile ohne nach rechts zu scrollen.

LOL

Liegt wohl daran, dass ich mich in meinen eigenen aktuellen
Makros selten für die rechten Enden der Zeilen interessiere.

Fehler, glaub mir. In der Testphase ist das natürlich okay beim aktuellen Projekt sowas zu tun. Aber wenn du in 2 Monaten in den ganzen Codes der Mappe was ändern willst so ist das teilweise wie Fremdcode.

Für dich mußte natürlich bei Codezeilen die du auch in 2 Monaten
problemlos schnell deuten kannst nix dazu schreiben. Aber bei den Codesequenzen die dir aktuell große Mühe machten sie zum laufen zu bringen so ist es in deinem Interesse wichtig da zumindest erklärende Stichworte o.ä. dazuzuschreiben.
Das mach sogar ich faule Socke *gg*

Aber man kann dem Gesamt-Range einen Wert zuweisen. Den haben
dann alle Zellen dort.

Ich hoffe ´mal, dass ich das niemals brauchen werde.

?, grad bei vielen gleichartigen Blättern ist das doch sehr sinnvoll.
Du markierst z.B. alle 12 Monatsblätter gleichzeitig manuell, dann schreibst du in A1 „Monatsbericht“, das steht dann in allen Blättern.
Das in Vba meinte ich.

Gruß
Reinhard

7

Hallo Reinhard,
… anonymisiert

„einigermaßen“?, sehr schön, vllt. ist ja noch was
intimgeheimes drin
was meine Neugierde erfreut :smile:

Tja, ich denke eben, dass man in den seltensten Fällen sicher sein kann, alle Kommentare, Kopf- und FußZeilen usw. gesäubert zu haben.

Liegt wohl daran, dass ich mich in meinen eigenen aktuellen
Makros selten für die rechten Enden der Zeilen interessiere.

Fehler, glaub mir. In der Testphase ist das natürlich okay
beim aktuellen Projekt sowas zu tun. Aber wenn du in 2 Monaten
in den ganzen Codes der Mappe was ändern willst so ist das
teilweise wie Fremdcode.

Für dich mußte natürlich bei Codezeilen die du auch in 2
Monaten
problemlos schnell deuten kannst nix dazu schreiben. Aber bei
den Codesequenzen die dir aktuell große Mühe machten sie zum
laufen zu bringen so ist es in deinem Interesse wichtig da
zumindest erklärende Stichworte o.ä. dazuzuschreiben.
Das mach sogar ich faule Socke *gg*

Ich mache das ja auch nicht absichtlich - Faulheit kenne ich auch :wink:

Aber man kann dem Gesamt-Range einen Wert zuweisen. Den haben
dann alle Zellen dort.

Ich hoffe ´mal, dass ich das niemals brauchen werde.

?, grad bei vielen gleichartigen Blättern ist das doch sehr
sinnvoll.
Du markierst z.B. alle 12 Monatsblätter gleichzeitig manuell,
dann schreibst du in A1 „Monatsbericht“, das steht dann in
allen Blättern.
Das in Vba meinte ich.

Kann ich mir so noch nicht konkret vorstellen. Bin schon froh, dass ich das nun mit der each-Schleife hinbekommen habe :smile:

Freundliche Grüße aus Oldenburg
Thomas

8

GegenBeispiel
Hallo Reinhard,

…:

Wie sähe das dann in A1-Schreibweise aus? ( Nur
interessehalber … )

Da gelten die gleichen Regeln.

Komischer- und ungerechterweise funktioniert aber folgender Code ohne Referenzierung der Range-Grenzen:

Sub alle_formeln() 'HauptProgramm
Call aeef
For Each blatt In Worksheets
If blatt.Name „Eingabe“ And blatt.Name „Liste“ Then
blatt.Range(„A1:M17“).FormulaR1C1 = „=Eingabe!RC“
blatt.Range(„B1:B6“).ClearContents
blatt.Range(„C6:G6“).ClearContents
blatt.Range(„c1“).ClearContents
blatt.Range(„k10“).ClearContents
blatt.Range(„B9:C15“).Locked = False
blatt.Range(„I4:I8“).Locked = False
blatt.Range(„I6“).Locked = True
blatt.Range(„I10“).Locked = False
blatt.Range(„J13:J15“).Locked = False
End If
Next blatt
Call aeet
End Sub

Oder habe ich da ´was übersehen?

Freundliche Grüße
Thomas

9

Hallo,
der obenstehende Code hat doch nicht funktioniert - es gab nur keine FehlerMeldung. Dementsprechend habe ich nun doch umgeschrieben:

Sub alle_formeln() 'HauptProgramm
Call aeef
For Each blatt In Worksheets
With blatt
If blatt.Name „Eingabe“ And blatt.Name „Liste“ Then
.Range(.Cells(1, 1), .Cells(17, 13)).FormulaR1C1 = „=Eingabe!RC“
.Range(.Cells(1, 2), .Cells(6, 2)).ClearContents
.Range(.Cells(6, 3), .Cells(6, 7)).ClearContents
.Cells(1, 3).ClearContents
.Cells(10, 11).ClearContents
.Range(.Cells(9, 2), .Cells(15, 3)).Locked = False
.Range(.Cells(4, 9), .Cells(8, 9)).Locked = False
.Cells(6, 9).Locked = True
.Cells(10, 9).Locked = False
.Range(.Cells(13, 10), .Cells(15, 10)).Locked = False
End If
End With
Next blatt
Call aeet
End Sub

So läuft´s.

Freundliche Grüße
Thomas