Referring back to the originating workbook in a macro.

[Don M.]

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

 

[Jim Thomlinson]

code that is executing is alwayr running in ThisWorkbook so

thisworkbook.Select

will select the workbook that is running the code.

 

[Dave Peterson]

Since one of the workbooks that you're using contains the macro, you can use
ThisWorkbook to refer to the workbook that owns that macro.

This may give you an idea:

Option Explicit
Sub ExportToTruckBinderyBoxReport()

Dim TBBRptWkbk As Workbook
Dim RngToCopy As Range
Dim DestCell As Range

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

With ThisWorkbook.Worksheets("canada box report")
Set RngToCopy = .Range("C5:s39")
End With

With TBBRptWkbk.Worksheets("canada box report")
Set DestCell = .Range("C5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues

TBBRptWkbk.Close savechanges:=False

Application.CutCopyMode = False

End Sub

 

[Dave Peterson]

Oops.

ThisWorkbook.Activate

code that is executing is alwayr running in ThisWorkbook so

thisworkbook.Select

will select the workbook that is running the code.

 

[Don M.]

Thank you guys, the ThisWorkbook.Activate statement worked perfectly.

Don