Macro: returning to original workbook

[anode505]

I have a few recorded macros that do a cut-n-paste to another file.

The cut file is called Service Report

The paste file is called tally

THe macro resides in Service report (which is a template and changes file names)

When I tried to add in a second c-n-p macro into the first (to simplify things) I get a error.

What it seems to be, looking at the code, is that focus doesn't return to the service report.

Is there a way to call the original file without a filename, since the orig file is dynamically named.
(can one 'chain' macros? I tried the call {macro}() but same error)

AH! is there a constant with the filename in it?

THanks

 

[anode505]

Ok, found it.

Application.ActiveWorkbook.Name

 

[JE McGimpsey]

I have a few recorded macros that do a cut-n-paste to another file.

The cut file is called Service Report

The paste file is called tally

THe macro resides in Service report (which is a template and changes file
names)

When I tried to add in a second c-n-p macro into the first (to simplify
things) I get a error.

What it seems to be, looking at the code, is that focus doesn't return to the
service report.

Is there a way to call the original file without a filename, since the orig
file is dynamically named.
(can one 'chain' macros? I tried the call {macro}() but same error)

AH! is there a constant with the filename in it?

It would probably have been helpful if you'd posted the relevant portion
of your macro, but perhaps something like:

Public Sub CNP()
Const csCopyToName As String = "Tally.xls"
Dim wbTo As Workbook

On Error Resume Next
Set wbTo = Workbooks(csCopyToName)
On Error GoTo 0
If wbTo Is Nothing Then
MsgBox "Open " & csCopyToName
Else
ThisWorkbook.Sheets(1).Range("A1:J10").Copy _
Destination:=wbTo.Sheets(1).Range("A1")
End If
End Sub

Adjust ranges as necessary.

Note that there's no need to change focus (i.e., activate/select).