Referring to filename in macro

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

The code below is part of a sub routine to import a text
file, copy it's contents, and paste them into the current
sheet. It works fine, until a user renames the sheet. How
can I modify the Windows(filename).Activate line to
make "filename" relative?


Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Select
Selection.Copy
===> Windows("Idoc Error Matrix Template.xls").Activate
Application.GoTo Reference:="Replace"
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
 
Beth,

The trick is to fire the macro while in the "target" workbook, store its
name in a variable, and then reference the workbook to copy back to by means
of the variable:

tgtwkbk = ActiveWorkbook.Name

'your code to open file and copy

Windows(tgtwkbk).Activate

'the rest of your code

HTH,
Nikos
 
Back
Top