Erst mal der Übersicht halber...
Sub
CommandButton2_Click()
Dim
WbDatei1
As
Workbook
Dim
WbDatei2
As
Workbook
Dim
strFilter
As
String
Dim
Wert1
As
Variant
Dim
Wert2
As
Variant
Dim
i
As
Integer
Set
WbDatei2 = ThisWorkbook
strFilter =
"Excel-Dateien(*.xls*), *.xls*"
ChDir "\\XXXXX\"
strFilter =
"Excel-Dateien(*.xls*), *.xls*"
strFileName = Application.GetOpenFilename(strFilter)
If
strFileName =
False
Then
Exit
Sub
Set
ActiveWorkbooks = Workbooks.Open(strFileName)
MsgBox
"Die Datei '"
& ActiveWorkbook.name &
"' wurde geöffnet."
, vbInformation,
"Hinweis"
With
Worksheets(
"Format"
)
For
i = 1
To
.Columns.Count
If
Cells(1, i) =
"Anlage"
Then
Wert1 = Range(Cells(2, i), Cells(1000, i))
End
If
If
Cells(1, i) =
"Preis pro kwh"
Then
Wert2 = Range(Cells(2, i), Cells(1000, i))
End
If
Next
i
End
With
ActiveWorkbooks.Close SaveChanges:=
False
Set
WbDatei2 = ThisWorkbook
With
ThisWorkbook.Worksheets(
"Tabelle1"
)
For
i = 1
To
.Columns.Count
If
Cells(1, i) =
"Anlage"
Then
Range(Cells(2, i), Cells(1000, i)) = Wert1
End
If
If
Cells(1, i) =
"Preis pro Kwh"
Then
Range(Cells(2, i), Cells(1000, i)) = Wert2
End
If
Next
i
End
With
Application.DisplayAlerts =
True
End
Sub