MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 10.05.2018, 14:55   #1
tom1963_1
Neuer Benutzer
Neuer Benutzer
Standard Excel 2013 - Tabellblattname in Bezug

Guten Tag zusammen,

vielleicht kann mir jemand bei meinem Problem helfen.

Im ersten Tabellenblatt (Name "2") nehme ich in einer Zelle per Verknüpfung Bezug auf eine andere Exceldatei auf Zelle A2

='[rezepte praeferenz.xls]KLB0033A'!$A$2

Im Tabellenblatt 2 (Name "3") müßte sich die gleiche Zelle statt auf $A$2 auf $A$3 beziehen. Tabellenblatt 3 (Name "4") auf $A$4 usw.

Nun möchte ich die Tabellblätter mehrfach (ca. 150x) kopieren. 2. Tabellblatt Name "3", 3. Tabellblatt Name"4" usw. - das funktioiert auch per VBA.

Wie bekomme ich jedoch den Bezug zum Tabellblatt in die Verknüpfung, damit ich nicht 150 mal suchen und ersetzen ausführen muß?

Ich müßte wissen, wie man hier ='[rezepte praeferenz.xls]KLB0033A'!$A$2 den Bezug $A$2 ersetzt durch $A$"Tabellenblattname"

Hoffe ich habe mich verständlich ausgedrückt und freue mich schon auf Rückmeldungen. Vielen Dank
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.05.2018, 17:29   #2
Werner.M
MOF User
MOF User
Standard

Hallo Tom,

zeig doch mal deinen Code mit dem du das Blatt kopierst.

Gruß Werner
Werner.M ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.05.2018, 18:54   #3
tom1963_1
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard Tabellenblattname in Bezug

Hallo Werner,

der Code sieht so aus:

Public Sub Tabellenblatt_kopieren()
Dim i As Long
For i = 2 To 150
Sheets("2").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = i + 1
Next
End Sub

funktioniert auch einwandfrei

Gruß Thomas
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.05.2018, 18:58   #4
tom1963_1
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard Tabellenblattname in Bezug

Vielleicht sollte ich noch hinzufügen, dass es sich um viele Zellen handelt, die mit einer anderen Datei verknüpft sind, aber im gleichen Tabellenblatt auf die gleiche Zeile der Verknüpften Datei zurückgreifen sollen.
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.05.2018, 05:17   #5
Werner.M
MOF User
MOF User
Standard

Hallo Tom,

teste mal:
PHP-Code:

Public Sub Tabellenblatt_kopieren()
Dim loAktuell As LongloAlt As LongAs Long

For 2 To 150
    Sheets
("2").Copy After:=Sheets(Sheets.Count)
    
ActiveSheet.Name 1
    loAktuell 
CLng(ActiveSheet.Name)
    
loAlt loAktuell 1
    Cells
.Replace What:="$A$" loAltReplacement:="$A$" loAktuell_
    LookAt
:=xlPartSearchOrder:=xlByRowsMatchCase:=False_
    SearchFormat
:=FalseReplaceFormat:=False
Next
End Sub 
Du solltest dir aber im Klaren sein, dass im eben erstellten Arbeitsblatt in allen vorhandenen Formeln jeweils $A$ Nummer Vorblatt durch $A$ Nummer aktuelles Blatt ersetzt werden.

Gruß Werner
Werner.M ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.05.2018, 07:25   #6
Werner.M
MOF User
MOF User
Standard nimm den hier...

Hallo Tom,

der vorige Code ist Quatsch - hatte einen Denkfehler. Nimm den hier:
PHP-Code:

Public Sub Tabellenblatt_kopieren()
Dim i As Long

For 2 To 10
    Sheets
("2").Copy After:=Sheets(Sheets.Count)
    
ActiveSheet.Name 1
    Cells
.Replace What:="$A$2"Replacement:="$A$" 1_
    LookAt
:=xlPartSearchOrder:=xlByRowsMatchCase:=False_
    SearchFormat
:=FalseReplaceFormat:=False
Next
End Sub 
Gruß Werner
Werner.M ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.05.2018, 07:34   #7
tom1963_1
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo Werner,

vielen Dank für die superschnelle Anwort.

Klappt super. Ich habe jedoch noch zig Bezüge, die auf andere Spalten in der verknüpften Datei zugreifen und diese sollten ebenfalls angepasst werden.

$B$2 sollte im nächsten Tabellenblatt $B$3 werden, $C$2 - $C$3, ... $GM$2 - $GM$3 usw.

Ist das machbar?

Ganz klasse wäre noch, wenn die Anzahl der zu kopierenden Tabellenblätter durch Usereingabe festgelegt wird. (Ist abhängig von den Auftragspositionen)

Vielen Dank schon mal für die Unterstützung und Grüße aus bayrisch Schwaben Thomas
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.05.2018, 09:18   #8
Werner.M
MOF User
MOF User
Standard

Hallo Tom,

dann z.B. indem du die ganzen Spalten bei denen die Anpassung der Zeilennummer erfolgen muss in einem Array erfasst.
Hier jetzt auch mit Eingabe der Anzahl der anzulegenden Tabellenblätter.

Code:

Public Sub Tabellenblatt_kopieren()
Dim i As Long, j As Long, loAnzahl As Long
Dim Suchen As Variant

'hier die ganzen Spalten erfassen bei
'denen die jeweilige Zeile angepasst
'werden muss
Suchen = Array("A", "B", "C")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

loAnzahl = Application.InputBox("Wieviele Blätter sollen erzeugt werden?", "Anzahl Blätter", , , , , , 1)
If loAnzahl = 0 Then Exit Sub

For i = 2 To loAnzahl + 1
    Sheets("2").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = i + 1
    For j = 0 To UBound(Suchen)
        Cells.Replace What:="$" & Suchen(j) & "$2", Replacement:="$" & Suchen(j) & "$" & i + 1, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next j
Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Gruß Werner
Werner.M ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.05.2018, 10:37   #9
tom1963_1
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo Werner,

sieht sehr gut aus, nur ich bekomme nicht alle Arrays in eine Zeile
und deshalb wird immer ein Syntaxfehler angezeigt.

Danke nochmal, das erspart uns eine Menge Arbeit

Wäre es möglich mit einem separaten Script die ganze Arbeitsmappe zu kopieren und Inhalte einfügen um eine erneute Aktualiserung der Daten zu vermeiden. Wäre klasse. Danke schon mal

Gruß Tom

Code:

Public Sub Tabellenblatt_kopieren()
Dim i As Long, j As Long, loAnzahl As Long
Dim Suchen As Variant

Suchen = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ", "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ",
"FA", "FB", "FC", "FD", "FE", "FF", "FG", "FH", "FI", "FJ", "FK", "FL", "FM", "FN", "FO", "FP", "FQ", "FR", "FS", "FT", "FU", "FV", "FW", "FX", "FY", "FZ", "GA", "GB", "GC", "GD", "GE", "GF", "GG", "GH", "GI", "GJ", "GK", "GL", "GM", "GN", "GO", "GP", "GQ", "GR", "GS", "GT", "GU", "GV", "GW", "GX", "GY", "GZ", "HA", "HB", "HC", "HD", "HE", "HF", "HG", "HH", "HI", "HJ", "HK", "HL", "HM", "HN", "HO", "HP", "HQ", "HR", "HS", "HT", "HU", "HV", "HW", "HX", "HY", "HZ", "IA", "IB", "IC", "ID", "IE", "IF", "IG", "IH", "II", "IJ", "IK", "IL", "IM", "IN", "IO", "IP", "IQ", "IR", "IS", "IT", "IU", "IV", "IW", "IX", "IY", "IZ")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

loAnzahl = Application.InputBox("Wieviele Blätter sollen erzeugt werden?", "Anzahl Blätter", , , , , , 1)
If loAnzahl = 0 Then Exit Sub

For i = 2 To loAnzahl + 1
    Sheets("2").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = i + 1
    For j = 0 To UBound(Suchen)
        Cells.Replace What:="$" & Suchen(j) & "$2", Replacement:="$" & Suchen(j) & "$" & i + 1, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next j
Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.05.2018, 20:27   #10
Werner.M
MOF User
MOF User
Standard

Hallo Tom,

PHP-Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As RangeCancel As Boolean)
Suchen = Array("A""B""C""D""E""F""G""H""I""J""K""L""M""N""O""P""Q"_
"R""S""T""U""V""W""X""Y""Z""AA""AB""AC""AD""AE""AF""AG""AH""AI"_
"AJ""AK""AL""AM""AN""AO""AP""AQ""AR""AS""AT""AU""AV""AW""AX""AY""AZ"_
"BA""BB""BC""BD""BE""BF""BG""BH""BI""BJ""BK""BL""BM""BN""BO""BP""BQ"_
"BR""BS""BT""BU""BV""BW""BX""BY""BZ""CA""CB""CC""CD""CE""CF""CG""CH"_
"CI""CJ""CK""CL""CM""CN""CO""CP""CQ""CR""CS""CT""CU""CV""CW""CX""CY"_
"CZ""DA""DB""DC""DD""DE""DF""DG""DH""DI""DJ""DK""DL""DM""DN""DO""DP"_
"DQ""DR""DS""DT""DU""DV""DW""DX""DY""DZ""EA""EB""EC""ED""EE""EF""EG"_
"EH""EI""EJ""EK""EL""EM""EN""EO""EP""EQ""ER""ES""ET""EU""EV""EW""EX"_
"EY""EZ""FA""FB""FC""FD""FE""FF""FG""FH""FI""FJ""FK""FL""FM""FN""FO"_
"FP""FQ""FR""FS""FT""FU""FV""FW""FX""FY""FZ""GA""GB""GC""GD""GE""GF"_
"GG""GH""GI""GJ""GK""GL""GM""GN""GO""GP""GQ""GR""GS""GT""GU""GV""GW"_
"GX""GY""GZ""HA""HB""HC""HD""HE""HF""HG""HH""HI""HJ""HK""HL""HM""HN"_
"HO""HP""HQ""HR""HS""HT""HU""HV""HW""HX""HY""HZ""IA""IB""IC""ID""IE"_
"IF""IG""IH""II""IJ""IK""IL""IM""IN""IO""IP""IQ""IR""IS""IT""IU""IV"_
"IW""IX""IY""IZ"
Zeilenumbrüche im Code mit Leerzeichen und _

Die neue Anforderung habe ich nicht verstanden.


Gruß Werner
Werner.M ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 08:37   #11
tom1963_1
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo Werner,

vielen Dank für deine Hilfe. Hat uns sehr geholfen.

Die letzte Anforderung wäre: gesamte Arbeitsmappe kopieren (Zwischenablage)
und dann Inhalte und Formatierungen einfügen, so dass die Formeln überschrieben werden.

Wenn der Aufwand zu groß ist, können wir das auch händisch machen.

Liebe Grüße

Tom
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2018, 09:16   #12
Werner.M
MOF User
MOF User
Standard

Hallo Tom,

wir sollten uns erst mal über die Begrifflichkeiten verständigen.

Die ganze Arbeitsmappe
Das wäre deine Excel-Datei, die aus zig Arbeitsblättern bestehen kann.

Ich vermute vielmehr, dass du bei einem Arbeitsblatt die Formeln durch die jeweiligen Werte ersetzen willst.

Dann so:
PHP-Code:

Public Sub werte()
With Worksheets("Tabelle1"Blattname anpassen
    
.Cells.Copy
    
.Cells(11).PasteSpecial Paste:=xlPasteValues
    Application
.CutCopyMode False
    
.Cells(11).Select
End With
End Sub 
Gruß Werner
Werner.M ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 06:35   #13
tom1963_1
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo Werner,

ich meinte schon die kpl. Arbeitsmappe aus zig Tabellenblättern

Gruß Tom
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 06:53   #14
RPP63neu
MOF Meister
MOF Meister
Standard

Moin und nur am Rande:
Ich mag mir gar nicht erst vorstellen, wie lange es gedauert hat
Array("A", "B", "C", "D", … , "IX", "IY", "IZ")
per Tastatur einzuhämmern.
Ich schätze grob eine Stunde nebst Sehnenscheideentzündung …
Ich habe es mal nachgestellt, hat mich ca. eine Minute gekostet:
Sub RPP()
Dim c&, i&
c = Range("IZ1").Column
Redim arrSpaltenBez(1 To c)
For i = 1 To c
  arrSpaltenBez(i) = Split(Cells(1, i).Address, "$")(1)
Next
End Sub


Gruß Ralf

__________________

Meine Logik war nicht fehlerhaft, nur meine Interpretation!
Tuvok

Geändert von RPP63neu (16.05.2018 um 07:00 Uhr).
RPP63neu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2018, 16:04   #15
tom1963_1
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

war halb so schlimm, mit suchen und ersetzen ging das relativ zügig, ohne gesundheitliche Folgeschäden
tom1963_1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 12:19 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.