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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows
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.......
 

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top