I've looked through the posts and I see lots of people with similar questions
to mine, I just can't figure out how to adapt the suggestions to my
application.

I have a workbook that is public on our network and anyone can open it, save
it, rename it. It contains a macro that copies cells in that workbook and
pastes them into my own workbook that doesn't change names. This public
workbook can be renamed to anything. I need the macro to remember what the
name of the workbook is so it can go out, paste values and then come back
into that same workbook no matter what it's been saved as.

I've read about this code

Dim WB As Workbook
Set WB = activeworkbook 'Change to active workbook

but I'm having problems getting it to work in my macro. I don't know enough
about the code to get the syntax right.

Let's say the Excel file is called NewFileName.xls and it contains this
macro. I need it to go open TruckBinderyBoxReport.xls, paste the data and
then come back to NewFileName.xls. The next time I run this macro the file
may not be called NewFileName.xls so the code has to figure out the file name
so it knows what to go back to.

Here's the macro as it is now.

Sub ExportToTruckBinderyBoxReport()

Sheets(Array("Canada Box Report", "USA Box Report")).Select
Sheets("Canada Box Report").Activate
Range("C5:S39").Select
Selection.Copy

Workbooks.Open Filename:= _
"\\fileserver\data\Bnd\Bindery
Library\Documents\B-line\TruckBinderyBoxReport.xls" _
, UpdateLinks:=3

Windows("TruckBinderyBoxReport.xls").Activate
Sheets(Array("Canada Box Report", "USA Box Report")).Select
Sheets("Canada Box Report").Activate
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Run Report").Select
Windows("TruckBinderyBoxReport UPS.xls").Activate
ActiveWindow.Close
End Sub

When I get to the line with Windows("TruckBinderyBoxReport
UPS.xls").Activate I get the error because the macro doesn't know the
workbook isn't called TruckBinderyBoxReport UPS.xls anymore. I've tried to
use Windows(WB).Activate and Windows(activeworkbook).Activate but I'm sure
the syntax is wrong so it just crashes.

Don