Hello
My procedure is running. A file is opened and then action is carried out with that opened file. It is all in one sub using three Input Boxes:
Code:
'...
If DateiOffen = False Then Workbooks.Open FileName:=dateiRev2
'...
abb = Application.InputBox("Ab welcher Spalte soll von Quelle kopiert werden?")
'...
biss = Application.InputBox("Ab welcher Spalte soll in Ziel eingefügt werden?")
'...
anzz = Application.InputBox("Wieviele Spalten sollen kopiert werden?")
'...
z = 0
For x = 1 To Range("A65536").End(xlUp).Row
For y = 1 To Range("A65536").End(xlUp).Row
t1 = Workbooks("Ziel.xls").Sheets("Tabelle1").Cells(x, 1)
t2 = Workbooks(dateiRev2).Sheets("Tabelle1").Cells(y, 1)
If t1 = t2 Then
' MsgBox t1
Workbooks("Ziel.xls").Activate
Set IDRow1 = Columns(1).Find(What:=t1, lookat:=xlWhole)
If Not IDRow1 Is Nothing Then
xzcord = IDRow1.Row
' MsgBox xzcord
End If
' MsgBox t2
Workbooks(dateiRev2).Activate
Set IDRow2 = Columns(1).Find(What:=t2, lookat:=xlWhole)
If Not IDRow2 Is Nothing Then
xqcord = IDRow2.Row
' MsgBox xqcord
End If
For i = 1 To anz
Workbooks("Ziel.xls").Sheets("Tabelle1").Cells(xzcord, i + bis - 1) = Workbooks(dateiRev2).Sheets("Tabelle1").Cells(xqcord, i + ab - 1)
Next
z = z + 1
End If
Next y
Next x
MsgBox z
Instead of having three input boxes, I introduced a userform with three text boxes. The macro is the same until the file is opened an then I load the userform:
If DateiOffen = False Then Workbooks.Open FileName:=dateiRev2
UserForm1.Show
End Sub
The code of the userform is (nearly) identical to the code in the first macro, only the variables, anz, ab, bis are replaced with the appropriate textboxes (respectively).
The only problem is to carry over the variable dateiRev2, since these are two modules, two different subs, the userform sub doesn't know dateiRev2 which was defined in the first sub.
I could write the entire procedure in the userform code, but I don't want the userform appear too early.
Code:
Private Sub CommandButton1_Click() ' in this bracket?
Workbooks("Ziel.xls").Activate
z = 0
For x = 1 To Range("A65536").End(xlUp).Row
For y = 1 To Range("A65536").End(xlUp).Row
t1 = Workbooks("Ziel.xls").Sheets("Tabelle1").Cells(x, 1)
t2 = Workbooks(dateiRev2).Sheets("Tabelle1").Cells(y, 1)
If t1 = t2 Then
' MsgBox t1
Workbooks("Ziel.xls").Activate
Set IDRow1 = Columns(1).Find(What:=t1, lookat:=xlWhole)
If Not IDRow1 Is Nothing Then
xzcord = IDRow1.Row
' MsgBox xzcord
End If
' MsgBox t2
Workbooks(dateiRev2).Activate
Set IDRow2 = Columns(1).Find(What:=t2, lookat:=xlWhole)
If Not IDRow2 Is Nothing Then
xqcord = IDRow2.Row
' MsgBox xqcord
End If
For i = 1 To TextBox3.Text
Workbooks("Ziel.xls").Sheets("Tabelle1").Cells(xzcord, i + TextBox2.Text - 1) = Workbooks(dateiRev2).Sheets("Tabelle1").Cells(xqcord, i + TextBox1.Text - 1)
Next
z = z + 1
End If
Next y
Next x
MsgBox z
Unload UserForm1
Workbooks("Ziel.xls").Activate
End Sub
I get error #9.
Taking over a filename to a userform
Linear Mode
