Macro To Activate Window / Workbook

almcrae

New Member
Joined
Apr 20, 2011
Messages
15
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,731
Try


Code:
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.......
 

Forum statistics

Threads
1,082,251
Messages
5,364,040
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top