Supportnet / Forum / Tabellenkalkulation

Hi Leute, mal wieder ich mit einem Problemchen: ich muss einen großen Bereich einer Tabelle in eine Tabelle in anderer Datei (!) kopieren; dabei sollen aber nur die Formeln bzw. Werte übernommen werden. Mit PasteValue bekomme ich Fehlermeldung bzw. ich brauche auf jeden Fall auch die Formeln in bestimmten Zellen; mit PasteAll werden auch Verknüpfungen zur Ausgangstabelle hergestellt; soll auch nicht sein. Es sollen nur die Formeln/Werte übernommen werden! Auszug aus meinem Quellcode: ...Worksheets("x").Range("A10:T1065").Copy Workbooks(Zieldatei).Worksheets("y)").Range("A10:T1065").PasteSpecial (xlPasteValues) Application.CutCopyMode = False... Bin für jeden Tipp dankbar!!! Viele Grüße, Richie

mit der Tastenkombination [Strg][,] lässt sich eine Formel 1:1 kopieren, ohne dass Excel den relativen Zellbezug anpasst. Allerdings lassen sich mit [Strg][,] lediglich der unmittelbar über der aktuell markierten Zelle befindliche Zellinhalt kopieren – wenn mehrere Zellen einer Zeile markiert sind, auch eine Reihe.

quick`n dirty so z.b.

for i = 1 to 25
if cells(i, 1) <> "" or cells(i, 1) <> 0 then
cells(i, 1) = worksheet(2).cells(i, 1)
next i

oder in deiner version so in etwa:

Sub kopieren()
Cells.Select
Selection.Copy
Sheets("Tabelle2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A6").Select
Sheets("Tabelle1").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub

Hallo Richie,

Verknüpfungen sind im Grundegenommen auch Formeln!
Es wird somit nicht möglich sein diese nicht mitzukopieren.

Gruß

JöKe

Hi Leute,
schonmal vielen Dank für die Tipps!
Mein Hauptproblem liegt darin, dass sich der Bereich, den ich kopieren will, auf andere Tabellenblätter innerhalb der Datei bezieht.
Wenn ich das ganze nun in das Tabellenblatt der neuen Datei kopiere, stellt Excel automatisch Verknüpfungen zur alten Datei her; sollte sich aber eigentlich auf die Tabellenblätter neuen Datei beziehen.

Ich fürchte, dass es da kaum eine Möglichkeit gibt, dass zu automatisieren.

Viele Grüße,
Richie

Hallo Richie,

es ist möglich ;-)

Option Explicit

Sub Richie()
Dim arr() As String, intZeile As Integer, i As Integer
intZeile = Cells(Rows.Count, 1).End(xlUp).Row
ReDim arr(intZeile)
For i = 1 To intZeile
arr(i) = Workbooks("Verknüpfung").Sheets("Tabelle2").Cells(i, 1).Formula
Next i
For i = 1 To intZeile
Workbooks("Verknüpfung neu").Sheets("Tabelle2").Cells(i, 1) = arr(i)
Next i
End Sub

Mit obenstehenden Code werden Formeln und Werte von Verknüpfung/Tabelle2/Spalte A nach Verknüpfung neu/Tabelle2/Spalte A übertragen. Die Formeln und Werte des Quellblattes werden dabei in ein Array eingelesen und im Zielblatt wieder ausgelesen.


MfG

JöKe

da du mehrere Spalten übertragen willst geht es so:

Option Explicit

Sub Richie()
Dim arr() As String, intZeile As Integer, i As Integer, loSpalte As Long
loSpalte = 1
Do While loSpalte <= 20
intZeile = Cells.SpecialCells(xlCellTypeLastCell).Row
ReDim arr(intZeile)
For i = 10 To intZeile
arr(i) = Workbooks("x").Sheets("Tabelle2").Cells(i, loSpalte).Formula
Next i
For i = 10 To intZeile
Workbooks("y").Sheets("Tabelle2").Cells(i, loSpalte) = arr(i)
Next i
loSpalte = loSpalte + 1
Loop
End Sub

Gruß

JöKe

PS: Beide Workbooks müssen geöffnet sein.

Hi JöKe!
auch an dich vielen Dank!!
Ich werd´s direkt mal ausprobieren!
Viele Grüße,
Richie

Hi JöKe!
Hab´s probiert; funktioniert aber leider noch nicht; bekomme Fehlermeldung "Index liegt außerhalb des Gültigkeitsbereichs; Fehler 9".

Habe folgenden Quellcode:

Dim arr() As String, intZeile As Integer, i As Integer, loSpalte As Long
loSpalte = 1
Do While loSpalte <= 6
intZeile = Cells.SpecialCells(xlCellTypeLastCell).Row
ReDim arr(intZeile)
For i = 1 To intZeile
arr(i) = Workbooks("Übung").Sheets("Tabelle2").Cells(i, loSpalte).Formula
Next i
For i = 1 To intZeile
Workbooks("Übung2").Sheets("Tabelle2").Cells(i, loSpalte) = arr(i)
Next i
loSpalte = loSpalte + 1
Loop

...ich wollte es so anpassen, dass Spalte 1-6 übernommen wird, sowie der komplette Bereich, in dem Werte enthalten sind (beginnend mit Zeile1);
ich weiß allerdings nicht, ob ich das richitg angepasst habe, da ich mit arrays leider 0 Erfahrung habe!

Vielleicht hättest du noch nen Tipp; wäre dir sehr dankbar!!!

Viele Grüße,
Richie

Hallo Richie,

soweit ich das sehe hast du alles richtig gemacht.
Hast du vielleicht nicht beide Workbooks offen gehabt?
Haben die Workbooks auch die Namen die du im Code verwendest?
Heißen die Sheets auch "Tabelle2" ?
Bei welcher Zeile im Code wird gemeckert?

Gruß

JöKe

Hallo!

Füg doch mal nach der Ermittlung der Zeilenzahl eine Messagebox ein, um diese Werte zu kontrollieren:

...
intZeile = Cells.SpecialCells(xlCellTypeLastCell).Row
Msgbox "intZeile = " & Cstr(intZeile), , "letzte Zeile in Spalte " & Chr(64 + loSpalte) & ":"
...

Ich habe den Verdacht, dass .SpecialCells(xlCellTypeLastCell) ein ziemlich unsicherer Befehl ist. Ich habe da schon seltsame Dinge erlebt, wenn der benutzte Bereich leere Zeilen, Spalten oder "Löcher" enthalten hatte.

Gruß,
CaroS

...Workbooks waren beide offen; Namen stimmen, Tabellennamen stimmen auch; gemeckert wird bei folgender Zeile:

...arr(i) = Workbooks("Übung").Sheets("Tabelle2").Cells(i, loSpalte).Formula ...

Viele Grüße,
Richie

Hi CaroS,

da ist anscheinend wirklich was faul; MsgBox zeigt als letzte Zeile (intZeile) 1 an, obwohl die Tabelle Werte bis Zeile 23 enthält...

Vielen Dank und Grüße,
Richie

Hallo Richie,

dann ersetze mal probeweise die Zeile

intZeile = Cells.SpecialCells(xlCellTypeLastCell).Row

durch

intZeile = Cells(65536, IoSpalte).End(xlUp).Row

Der Rest ist wahrscheinlich in Ordnung.

Gruß,
CaroS

Hallo,

kann CaroS nur Recht geben. ;-)
Allerdings in hinblick auf künftige Office-Versionen ist:

intZeile=Cells(Rows.count, loSpalte).End(xlUp).Row

besser.


Gruß

JöKe

Wenn der zuübertragene Bereich fest bleibt, also immer Zeile 1 - 23, kann auf intZeile auch ganz verzichtet werden.
Dafür die Zeile:

intZeile = Cells.SpecialCells(xlCellTypeLastCell).Row

ganz raus.

Die Zeilen:

ReDim arr(intZeile)
For i = 1 To intZeile (diese gibt es 2x)

durch:

ReDim arr(23)
For i = 1 To 23

ersetzen.


Gruß

JöKe

Hallo Richie,

inzwischen glaube ich nicht mehr so sehr, dass es an intZeile liegt. Selbst wenn der Wert fälschlicherweise immer nur bei 1 liegt, müsste der Code zumindest "technisch" fehlerfrei laufen.

Kannst Du im Debug-Modus (oder mit Hilfe zusätzlich eingebauter Msgbox- oder Debug.Print-Anweisungen) mal feststellen, bei welcher Zelle (i, IoSpalte) es zu dem Fehler kommt und ob die Zelle leer ist?

arr(i) = Workbooks("Übung").Sheets("Tabelle2").Cells(i, loSpalte).Formula

Gruß,
CaroS

...klappt leider auch nicht; ich bekomme als Anzeige für die letzte Zeile immer 1; keine Ahnung warum;
ich versuche mal was anderes!

Vielen Dank für Eure Hilfe/Mühe!!!
Gruß,
Richie

Hallo Richie,

welche Excel - Version hast du?

...habe Excel 2003...

dürfte daran eigentlich nicht liegen.
Aber ein versuch ist es Wert.
Versuch es mal so:

Option Explicit

Sub richie()
Dim arr() As String, intZeile As Integer, i As Integer, loSpalte As Long
loSpalte = 1
Do While loSpalte <= 6
intZeile = IIf(IsEmpty(Cells(Rows.Count, loSpalte)), Cells(Rows.Count, loSpalte).End(xlUp).Row, Rows.Count)
ReDim arr(intZeile)
For i = 1 To intZeile
arr(i) = Workbooks("Übung").Sheets("Tabelle2").Cells(i, loSpalte).Formula
Next i
For i = 1 To intZeile
Workbooks("Übung2").Sheets("Tabelle2").Cells(i, loSpalte) = arr(i)
Next i
loSpalte = loSpalte + 1
Loop
End Sub

Bzw. was geschied wenn du, wie in AW 14 beschrieben, die Zeilen fest vorgibst?

Gruß

Hallo Ihr beiden!!!

Vielen Dank für Eure Hilfe!!! Es funktioniert!!!


Private Sub CommandButton2_Click()

Dim arr() As String, intZeile As Integer, i As Integer, loSpalte As Long
loSpalte = 1
Do While loSpalte <= 6
ReDim arr(23)
For i = 1 To 23
arr(i) = Workbooks("Übung.xls").Sheets("Tabelle2").Cells(i, loSpalte).Formula
Next i
For i = 1 To 23
Workbooks("Übung2.xls").Sheets("Tabelle2").Cells(i, loSpalte) = arr(i)
Next i
loSpalte = loSpalte + 1
Loop

End Sub

Wie ihr wahescheinlich gemerkt habt, bin ich noch blutiger Anfänger, was VBA-Progr. angeht; deshalb kann ich auch nicht genau sagen, woran es im Endeffekt lag :-)! Aber es funktioniert!!!

Viele Grüße und bis wahrscheinlich sehr bald bei meinem nächsten Excel-Problem :-)!!!
Richie

Hallo Richie,

schöne Idee, es bei der Datei mal mit .xls zu versuchen! Das könnte es gewesen sein. Naja, man sieht eben auch nicht alles.

arr(i) = Workbooks("Übung.xls"). ...

@nighty:
nighty, wann gehen wir zusammen zum Optiker?

Grüße an alle!
CaroS

• Werte kopieren - VBA Makroerweiterung!!!
• anzeigen von Verknüpfungen und Formeln
• Verknüpfungen in Access
• Formeln unterdrücken
• Formeln deaktivieren