Macro won't run - WrkBk name keeps changing

[Wheelie686]

The following code unhides and copies the "Data" tab from a doc called Aliant Call Profile Report (End of Day) and then pastes it into a tab called NBReport Data on a document that doesn't have a set name so I've used ActiveSheet.Paste. The problem I have is now Aliant Call Profile Report (End of Day) is no longer the name. Excel seems to want to continue adding (1),(2),(3), etc... to the end of my filename. I know about the excel /unregserver but that did not solve my problem. Is there a way to modify my code to account for the fact that my filename won't stay the same?

Windows("Aliant Call Profile Report (End of Day) .xls").Activate
Sheets("Data").Visible = True
ActiveWorkbook.Activate
Sheets("Data").Select
Range("A1:Z533").Activate
Selection.Copy
ThisWorkbook.Activate
Sheets("NBReport Data").Select
ActiveCell.Cells.Select
ActiveSheet.Paste
Sheets("Control").Select
Range("A1").Select
End Sub

[davesexcel]

ThisWorkbook and Activeworkbook are probably considered the same workbook. You will have to use the destination workbook name for thisworkbook

[Wheelie686]

Thought I'd share the code that actually worked for me. Thanks for your reply though Dave.

Sub GetNBReport()
Application.ScreenUpdating = False
For Each w In Workbooks
If w.Name Like "*Aliant Call Profile Report*" Then
Windows(w.Name).Activate
Exit For
End If
Next w
Sheets("Data").Visible = True
ActiveWorkbook.Activate
Sheets("Data").Select
Range("A1:Z533").Activate
Selection.Copy
ThisWorkbook.Activate
Sheets("NBReport Data").Select
ActiveCell.Cells.Select
ActiveSheet.Paste
Sheets("Control").Select
Range("A1").Select
Windows(w.Name).Close
Application.ScreenUpdating = True
End Sub