Thema: PasteSpecial funktioniert nicht | Herbers Excel-Forum

Ich habe ein altes Makro, welches "FileSearch" enthielt erfolgreich an Excel 2007 angepasst.
Leider hat das Makro einen kleinen Schönheitsfehler: Von den Werten, welche aus den externen Excel-Dateien in die eigene Tabelle kopiert werden, kann keine Summe gebildet werden.
Das Problem lässt sich lösen, wenn statt einem gewöhnlichen Paste ein PasteSpecial Paste:=xlPasteValues verwendet wird.
Offenbar kann ich aber nicht die Worksheet.PasteSpecial-Methode verwenden, sondern muss auf die Range.PasteSpecial-Methode ausweichen.
Ich habe alles mögliche versucht und das Script verschiedentlich angepasst, aber habe es einfach nicht hingekriegt. Ein Problem scheint zu sein, dass beim Wechsel von einer Datei zur anderen, wo einfegügt werden soll, das Clipboard seinen Inhalt verliert... oder so ähnlich.
Hat jemand einen Tipp, wie ich da hinkriegen könnte.

Option Explicit
Option Compare Text
Sub zusammensetzen()
Dim i
Dim myfolder
Dim mytabelle
Dim myrange
Dim mylines
Dim offset
Dim myfilename
Dim fso As Object
Dim fol As Object
Dim fil As Object
'Where you define what to "zusammensetzen"
myfolder = Range("D4")              'Pfad zu den Arbeitsrapporten
mytabelle = Range("D6")             'Name der zu bearbeitenden Tabelle
myrange = Range("D9")               'Bereich in der Tabelle, welcher kopiert werden soll ( _
Feldwert(e)):
mylines = Range(myrange).Rows.Count 'zum Berechnen der Zeile zum nächsten Einfügen
offset = Range("D11")               'Position, bei welcher mit Einfügen begonnen werden soll
i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(myfolder)
For Each fil In fol.Files
i = i + 1
Workbooks.Open Filename:=fil
myfilename = Right(fil, Len(fil) - Len(myfolder))
Sheets(mytabelle).Activate
Range(myrange).Copy
ActiveWorkbook.Close False
ActiveSheet.Paste Destination:=Worksheets("Auswertung").Range(offset).offset(i * mylines -  _
mylines, 1)
Worksheets("Auswertung").Range(offset).offset(i * mylines - mylines).Value = myfilename
Next fil

Hi
es kann durchaus sein, daß Excel beim Schließen der Datei seine interne Zwischenablage löscht.
Daraus folgt, daß du die Daten erst einfügen musst, bevor du die Quelldatei schließt.
Dh. .PASTE (bzw .PASTESPECIAL) muss vor ActiveWorkbook.Close erfolgen.
allerdings musst du dann bei .PASTE vollständig referenzieren, da Workbook und Sheet mit angeben.
Gruß, Daniel

Danke für den enorm schnellen Hinweis!
Wie kann ich den auf die Arbeitsmappe in welcher das Makro enthalten ist zugreifen?

DieseArbeitsmappe.Worksheets("Auswertung").Range(offset).offset(i * mylines - mylines, 1). _
PasteSpecial Paste:=xlPasteValues

funktioniert nicht.
Mit ActiveWorkbook.Close False dachte ich, sollte verhindert werden, dass die Zwischeablage geleert wird, aber offenbar funktioniert das nicht. Es genügt das Arbeitsmappe zu wechseln, um die Daten zu verlieren.
Falls es wirklich das wäre. Tatsächlich kann ich aber nach der Fehlermeldung auf die Arbeitsmappe wechseln eine Position selektieren und mit Ctrl (Strg) + V den aktuellen und richtigen Inhalt vom Clipboard einfügen.

referenziert die Arbeitsmappe, in der der Code steht.
Gruß, Daniel

Danke!
ActiveWorkbook.Close False macht wohl genau das Gegenteil von dem was ich erwartet habe:
Es schliesst die Aktive Arbeitsmappe
Ich habe den Code hingekriegt.
Nun wäre es noch schön am Schluss direkt die Summe auszugeben.
Das habe ich versucht mit: .FormulaR1C1 = "=SUM(R[-i]C:R[-1]C)
aber dieser Teil des Makros führt nur zu einer Fehlermeldung: Anwendungs- oder objektdefinierter Fehler.
Naja. Ich werde mich vielleicht später noch einmal damit auseinandersetzen. Jetzt ist Zeit zum Schlafen.
Hier noch der funktionierende Code (ausser den letzten 3 Zeilen):
Hinweise, wie ich die Summe hinkriegen kann oder über anderweitige Verbesserungen nehme ich dankbar entgegen.

Option Explicit
Option Compare Text
Sub zusammensetzen()
Dim i
Dim myfolder
Dim mytabelle
Dim myrange
Dim mylines
Dim offset
Dim myfilename
Dim fso As Object
Dim fol As Object
Dim fil As Object
'Where you define what to "zusammensetzen"
myfolder = Range("D4")              'Pfad zu den Arbeitsrapporten
mytabelle = Range("D6")             'Name der zu bearbeitenden Tabelle
myrange = Range("D9")               'Bereich in der Tabelle, welcher kopiert werden soll ( _
Feldwert(e)):
mylines = Range(myrange).Rows.Count 'zum Berechnen der Zeile zum nächsten Einfügen
offset = Range("D11")               'Position, bei welcher mit Einfügen begonnen werden soll
i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(myfolder)
For Each fil In fol.Files
i = i + 1
Workbooks.Open Filename:=fil
myfilename = Right(fil, Len(fil) - Len(myfolder))
Sheets(mytabelle).Activate
Range(myrange).Copy
ThisWorkbook.Worksheets("Auswertung").Range(offset).offset(rowOffset:=(i * mylines -  _
mylines), columnOffset:=1).PasteSpecial Paste:=xlPasteValues
ThisWorkbook.Worksheets("Auswertung").Range(offset).offset(i * mylines - mylines).Value =  _
myfilename
ActiveWorkbook.Close False
Next fil
ThisWorkbook.Worksheets("Auswertung").Activate
ThisWorkbook.Worksheets("Auswertung").Range(offset).offset(rowOffset:=(i * mylines -  _
mylines + 2), columnOffset:=1).FormulaR1C1 = "=SUM(R[-i]C:R[-1]C)"
End Sub

HI
1. es ist in der Regel keine gute Idee, einer Variablen den die gleiche Benennung zu geben wie einer VBA-Funktion (hier "offset")
2. wenn man den Wert einer Variablen in einen String einfügen will, dann muss man das so machen

Dim i as long
dim txt as string
i = 100
txt = "Hier soll jetzt der Wert der Variablen i stehen: " & i & " dh die Zahl einhundert"

dies hätte als Ergebnis Hier soll jetzt der Wert der Variablen i stehen: 100 dh die Zahl _
einhundert
dieser Code

Dim i as long
dim txt as string
i = 100
txt = "Hier soll jetzt der Wert der Variablen i stehen:  i  dh die Zahl einhundert"

hätte als Ergebnis: Hier soll jetzt der Wert der Variablen i stehen: i dh die Zahl einhundert
ich hoffe es ist klar, das die Formel mit dem i nicht funktioniert, sondern nur mit dem Zahlenwert der Variablen i
Gruß, Daniel

Danke Daniel
zu 1) Du hast vollkommen Recht. Keine Ahnung wie ich auf diesen Namen kam, der ist noch nicht einmal besonders selbsterklärend. Habe ihn geändert.
zu 2.)
Super und einleuchtend.
Ich habe einen String schon versucht mit "." zusammenzusetzen, aber das war PHP Code und nicht VBA.
Nun funktionierts. Cool!
Danke vielmals für dein Antworten. Das hat mir sehr geholfen.