Move sheet to another document with variable name

[gmunro]

Hello,

I am trying to find a way to move a worksheet to another document. The
document named is in a cell.

Sub MoveSheet()
'
' Range("B27").Activate
ActiveWindow.DisplayWorkbookTabs = True

Range("B5").Select
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = [b5]

ActiveSheet.Move After:=Workbooks("John Smith.xls").Worksheets(1)

End Sub

All files that I will be moving to will have the assocaites name, but
next time the Person will be Jane Smith or Joe Browm.

I would like to move the sheet to [A1].xls
Is this possible?

Glen Munro

 

[Guest]

Here is the code...

Sub MoveSheet()

ActiveWindow.DisplayWorkbookTabs = True
ActiveSheet.Name = ActiveSheet.Range("B5").Value
ActiveSheet.Move
After:=Workbooks(ActiveSheet.Range("A1").Value).Worksheets(1)

End Sub

Note the Cell A1 must contain the full name of the excel file "John
Smith.xls" and that that workbook must be open... Otherwise the code will die
when the move is attempted...

HTH