windows("variable filename").activate

Good morning guys, hope you can help me tackle a problem I'm having wth my macro.....

I have created a macro to split out information from one excel workbook into 2 additional workbooks, and format them the way we need. Everything works fine on the filename I originally recorded the macro from, but is giving me a 'subscript out of range' error when trying to run the macro on a file with a different name.

currently, my test file has the 2 following fields that I somehow need to have as variables:

Windows("insmacrotest20090418.xls").Activate

and

Windows("Book1").Activate

I need the filenames and workbooks in the parenthesis to be variable so that the macro can be run on other reports with varying filenames.

Please help me out with this, as you can tell by the file name, I've been fighting with this macro off & on for over a month now.

thanks!!!!

sorry, I'm still new with VBA.... I've tried the way you had listed & then a couple different configurations and continued to receive the debug message.

here is my script, please tell me where I'm going wrong:

Please Login or Register  to view this content.

the last line is where my debugger goes everytime....

I dont see that insmacrotest20090418 has been set in that code (unless it's in a subroutine you're calling)

stevo,

Please edit your post to add code tags.

stevo1329
My English very bad, so ..small talk.
Dry something like this:

Sub ava()
Dim MyBookName As String

' declares the variable, right now it contains an empty string
Dim Workbook As Workbook

MyBookName = "insmacrotest20090418" ' this sets the value of the variable to the sheet name

ActiveWindow.Caption = "1"

Columns("C:C").Select
Selection.Cut

'you code
Workbooks.Add 'This wbk first named Book1, Later("2")
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit

ActiveWindow.Caption = "2"
'Do something

Windows("1").Activate
'Do something
Windows("2").Activate
'Do something
Windows("1").Activate
'Do something
Windows("2").Activate
'Do something


' Rename as you need , Save, Close.
End Sub

More common practice would be to activate the Workbook rather than the Window:

Please Login or Register  to view this content.

or as Cyclops suggested,

Please Login or Register  to view this content.