Recently Upgraded to Excel 2010 and now My 2007 Macros will not work

[SNeaK930]

Hello,
Below is a macro that i had written in 2007 that oes not work anymore and i have no idea why. It gets as far as opening the first file but then stops with no error. It's supposed to open a file browser, then i click on the first of 3 files then it copies the data and pastes it back into the main file. If someone could please help it would be much appreciated. Im not very good with VBA, i taught what little i know from google.

Thank you


Sub combinefinal()
'
' combinefile Macro
' Macro recorded 1/31/2011 by pb
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Dim outputcells As Excel.Range

On Error GoTo err102:
Application.ScreenUpdating = False
'incase of an error the macro will go to errhandler and perform end sub

FilesToOpen = Application.GetOpenFilename _
(filefilter:="microsoft excel files (*xls), *.xls", _
MultiSelect:=True, Title:="files to merge")
'allows the macro to open up xls type files. if files change you can change them here

If TypeName(FilesToOpen) = "boolean" Then
MsgBox "no files were selected"
GoTo exithandler
End If

X = 1
While X <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(X)
Sheets("Activity Summary").Select
Range("A1:I200").Select
Selection.copy
Windows("BANK ANALYSIS fy12.xlsm").Activate
Sheets("CAP CORP").Select


Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 2).Select
ActiveSheet.Paste
FilesToOpen = Application.GetOpenFilename _
(filefilter:="microsoft excel files (*xls), *.xls", _
MultiSelect:=True, Title:="files to merge")
'allows the macro to open up xls type files. if files change you can change them here

If TypeName(FilesToOpen) = "boolean" Then
MsgBox "no files were selected"
GoTo exithandler
End If

X = 1
While X <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(X)
Sheets("Activity Summary").Select
Range("A1:I200").Select
Selection.copy
Windows("BANK ANALYSIS FY12.xlsm").Activate
Sheets("COLLECTION").Select

Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 2).Select
ActiveSheet.Paste

FilesToOpen = Application.GetOpenFilename _
(filefilter:="microsoft excel files (*xls), *.xls", _
MultiSelect:=True, Title:="files to merge")
'allows the macro to open up xls type files. if files change you can change them here

If TypeName(FilesToOpen) = "boolean" Then
MsgBox "no files were selected"
GoTo exithandler
End If

X = 1
While X <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(X)
Sheets("Activity Summary").Select
Range("A1:I200").Select
Selection.copy
Windows("BANK ANALYSIS fy12.xlsm").Activate
Sheets("INS").Select


Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 2).Select
ActiveSheet.Paste

exithandler:
Application.ScreenUpdating = True
Exit Sub
err102:
MsgBox Err.Description
Resume exithandler

Wend
Wend
Wend
End Sub

 

[EB08]

Without reading through the code, can you please clarify where the code fails to deliver on the desired functionality? Does it display the open file dialog that you mention in your description?

 

[SNeaK930]

it allows me to select a file from the browser dialog box, it then opens the file, and then it does nothing. It doesn not perform the copy operation.

 

[EB08]

My initial guess, without having the documents in front of me, is that there's something wrong with a sheet or workbook reference (possibly a sheet name with an additional space?); can't say for sure at this point, but I would expect the msgbox to throw the 'subscript out of range' error if that were the case. Have you tried stepping through the macro line by line using the F8 key to see exactly where it fails?

 

[SNeaK930]

I used the step through method (F8) and the Macro works fine it does not fail at any point.

 

[SNeaK930]

i noticed then when i run the macro the first file it opens to extract the data has design mode turned on. i tried to turn it off and then save it but whenever the macro opens the file the design mode gets turned on. this only happens when i run the macro and not when i use the step through methid