Navigate between 2 workbooks
 

I will have 2 workbooks open and wish to move backwards and forwards between
them.

One operation that needs to be carried out is to copy from one and paste to
the other and there may be other things as development progresses.

Just wondering what is the best method of reliably moving from one to the
other and back again?
--
Graham

Navigate between 2 workbooks
 

Give yourself some variables to work with, helping to make it clear what you
are doing and avoiding the need for .Activate and/or .Select.

Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet

Set SourceWB = Workbooks.Open(PathAndFileToSourceWB)
Set DestinationWB = Workbooks.Open(PathAndFileToDestinationWB)

With SourceWB
Set DestinationWS = DestinationWB.Worksheets("Destination1")
With .Worksheets("Source1")
.Range("A1").Copy Destination:=DestinationWS.Range("A1")
End With

With .Worksheets("Source2")
.Range("A1").Copy Destination:=DestinationWS.Range("A2")
End With

End With

NickHK

"Graham" wrote in message
...
I will have 2 workbooks open and wish to move backwards and forwards
between
them.

One operation that needs to be carried out is to copy from one and paste
to
the other and there may be other things as development progresses.

Just wondering what is the best method of reliably moving from one to the
other and back again?
--
Graham

Navigate between 2 workbooks
 

Thanks Nick

I can see the logic in that.

But, the Destination WB will already be open as that will be the one with
the VBA code - will that make much difference?
--
Graham


"NickHK" wrote:

Give yourself some variables to work with, helping to make it clear what you
are doing and avoiding the need for .Activate and/or .Select.

Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet

Set SourceWB = Workbooks.Open(PathAndFileToSourceWB)
Set DestinationWB = Workbooks.Open(PathAndFileToDestinationWB)

With SourceWB
Set DestinationWS = DestinationWB.Worksheets("Destination1")
With .Worksheets("Source1")
.Range("A1").Copy Destination:=DestinationWS.Range("A1")
End With

With .Worksheets("Source2")
.Range("A1").Copy Destination:=DestinationWS.Range("A2")
End With

End With

NickHK

"Graham" wrote in message
...
I will have 2 workbooks open and wish to move backwards and forwards
between
them.

One operation that needs to be carried out is to copy from one and paste
to
the other and there may be other things as development progresses.

Just wondering what is the best method of reliably moving from one to the
other and back again?
--
Graham

Navigate between 2 workbooks
 

Graham,
OK, you can use ThisWorkbook
or
Set DestinationWB = ThisWorkbook
if your want be clear

NickHK

"Graham" wrote in message
...
Thanks Nick

I can see the logic in that.

But, the Destination WB will already be open as that will be the one with
the VBA code - will that make much difference?
--
Graham


"NickHK" wrote:

Give yourself some variables to work with, helping to make it clear what
you
are doing and avoiding the need for .Activate and/or .Select.

Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet

Set SourceWB = Workbooks.Open(PathAndFileToSourceWB)
Set DestinationWB = Workbooks.Open(PathAndFileToDestinationWB)

With SourceWB
Set DestinationWS = DestinationWB.Worksheets("Destination1")
With .Worksheets("Source1")
.Range("A1").Copy Destination:=DestinationWS.Range("A1")
End With

With .Worksheets("Source2")
.Range("A1").Copy Destination:=DestinationWS.Range("A2")
End With

End With

NickHK

"Graham" wrote in message
...
I will have 2 workbooks open and wish to move backwards and forwards
between
them.

One operation that needs to be carried out is to copy from one and
paste
to
the other and there may be other things as development progresses.

Just wondering what is the best method of reliably moving from one to
the
other and back again?
--
Graham

Navigate between 2 workbooks
 

Thanks Nick for yor prompt and clear help
--
Graham


"NickHK" wrote:

Graham,
OK, you can use ThisWorkbook
or
Set DestinationWB = ThisWorkbook
if your want be clear

NickHK

"Graham" wrote in message
...
Thanks Nick

I can see the logic in that.

But, the Destination WB will already be open as that will be the one with
the VBA code - will that make much difference?
--
Graham


"NickHK" wrote:

Give yourself some variables to work with, helping to make it clear what
you
are doing and avoiding the need for .Activate and/or .Select.

Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet

Set SourceWB = Workbooks.Open(PathAndFileToSourceWB)
Set DestinationWB = Workbooks.Open(PathAndFileToDestinationWB)

With SourceWB
Set DestinationWS = DestinationWB.Worksheets("Destination1")
With .Worksheets("Source1")
.Range("A1").Copy Destination:=DestinationWS.Range("A1")
End With

With .Worksheets("Source2")
.Range("A1").Copy Destination:=DestinationWS.Range("A2")
End With

End With

NickHK

"Graham" wrote in message
...
I will have 2 workbooks open and wish to move backwards and forwards
between
them.

One operation that needs to be carried out is to copy from one and
paste
to
the other and there may be other things as development progresses.

Just wondering what is the best method of reliably moving from one to
the
other and back again?
--
Graham