Copying text from one file to the other

Hello

I want to copy certain cells from a file called "Source.xls" to certain cells in "Target.xls".

The macro will be written in Target.xls.
How do I access a data from Source.xls while I am in Target.xls?

Sheets(Source.xls!"Table1").Activate ? Both files are in the same folder.

I thought this will colve the problem, but this only works if both files are open.

What do I have to change to make it always work, no matter which files are open?

Ziel = Target
Quelle = Source


Sub Kopie()

'On Error GoTo errorhandler

Workbooks("Ziel.xls").Activate

SuchZahl = Application.InputBox("Ident Nummer eingeben: ")

If SuchZahl = "Falsch" Then Exit Sub

If SuchZahl = "" Then
MsgBox ("Fehler: Es wurde keine Suchzahl eingegeben.")
Exit Sub
End If

If IsNumeric(SuchZahl) = False Then
MsgBox ("Fehler: SuchZahl muss nummerisch sein.")
Exit Sub
End If

Dim IDRow As Range
Set IDRow = Columns(1).Find(What:=SuchZahl, lookat:=xlWhole)
If Not IDRow Is Nothing Then
xzcord = IDRow.Row
MsgBox xzcord
Else
MsgBox ("ID Nummer nicht gefunden")
Exit Sub
End If

Workbooks("Quelle.xls").Activate
Set IDRow = Columns(1).Find(What:=SuchZahl, lookat:=xlWhole)
If Not IDRow Is Nothing Then
xqcord = IDRow.Row
MsgBox xqcord
Else
MsgBox ("ID Nummer nicht gefunden")
Exit Sub
End If

For i = 1 To 6
Workbooks("Ziel.xls").Sheets("Tabelle1").Cells(xzcord, i + 3) = Workbooks("Quelle.xls").Sheets("Tabelle1").Cells(xqcord, i + 2)
Next

Workbooks("Ziel.xls").Activate

Exit Sub
'errorhandler:
'MsgBox "Suchbegriff fehlerhaft."

End Sub

You can only .Activate the workbook once it is in the WorkBooks collection. So either .ADD it, or .OPEN it first.

Like this:

Sub Kopie()

Workbooks("Quelle.xls").Open 'or .Add

Workbooks("Ziel.xls").Activate

SuchZahl = Application.InputBox("Ident Nummer eingeben: ")

If SuchZahl = "Falsch" Then Exit Sub
...
...

or Open Workbooks("Quelle.xls") For ..... as ....

The following error occured: Error 9, outwith the valid range.

Edit: No, like this:

Workbooks.Open FileName:="G:\Quelle.xls"

"Workbooks.Add" will add a new workbook to the collection - probably not what you want.

You probably want:

Workbooks.Open "YourFileName.XLS"
Workbooks("YourFileName.XLS").Activate

Can you introduce a check at the beginning of the macro, check whether the second Workbook is already open, if not, open it, if yes, leave it.

If the first Workbook is opened/closed then the second workbook should automatically be opened/closed as well. Is this possible?

Workbooks.Count ??

For each MyBook in Workbooks.Workbook
If myBook.Name = "MyWorksheet.XLS" then bFoundIt = "YES"
Next
If bFoundIt = "YES" then MsgBox "Its already in the collection"