Macro To Activate Window / Workbook

[almcrae]

Hi There

I am new to writing macros and having a prolem getting excel to activate a window.

Background: - Excel 2007, Windows XP

I have one file called NEW PB SPREADSHEET 10-11.xlsm with multiple tabs. I want the user to click a button that will invoke the macro. The macro presents the user with an input box where they type in a name ie MARCH. The macro then creates a new workbook using this name as the name of the new workbook.
It then goes back to NEW PB SPREADHSHEET 10-11.xlsm to start copying data from 4 tabs and paste special values in the new workbook.

What I am having issues with is calling the new workbook back. I get the following error:

Run-time error '9':
Subscript out of range.
Here is my code. Any help is appreciated. Im sure its a pretty easy solution but I just can't find anything online that explains how to call back and activate a window from a variable that has already been used...
TIA
Ants

Sub CopyTabs()
'
' CopyTabs Macro
'
Dim NewName As String
'
NewName = InputBox(Prompt:="Please enter a name to SAVE AS.")
Workbooks.Add
ActiveWorkbook.saveas "" & NewName

Windows("NEW PB SPREADSHEET 10-11.xlsm").Activate
Sheets("DASHBOARD").Select
Cells.Select
Selection.Copy

Windows(NewName).Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("NEW PB SPREADSHEET 10-11.xlsm").Activate
Sheets("KPI SCORECARD").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy

Code goes on.....

 

[daverunt]

Try

ActiveWorkbook.saveas "" & NewName
[COLOR=sienna]Set XLSfile = ActiveWorkbook[/COLOR]
Windows("NEW PB SPREADSHEET 10-11.xlsm").Activate
Sheets("DASHBOARD").Select
Range("A1").Select
Selection.Copy
[COLOR=sienna]XLSfile.Activate[/COLOR]
Selection.PasteSpecial.......