Hi all,
been running a macro for quite some time in excel 2007 suddenly it has now developed a problem without it being amended in anyway, keep getting errors at various lines many involving subscript errors. I know activate is not the best command in the vba world to use but I am not a vba pro. selction of the code is below, any help most appreciated as I'm getting grief about this from several people.
Sub TEST3()
'Sub AUTO_OPEN()
Dim fPath As String
Dim fName As String
Dim xname As String
Dim Cnt As Long
Dim WB As Workbook
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
'PATH TO WHERE THE EXCEL FILES ARE HELD
fPath = "\\HAWKEYE\Company\DAVEM\WALES\"
fName = Dir(fPath & "*.xls")
xname = Replace(fName, ".xls", "")
Cnt = 0
'MsgBox "THE NAME OF THE ACTIVE DIRECTORY IS " & fPath & " MSSG 1"
MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & fName & " MSSG 2"
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 3"
While Len(fName) > 0
Workbooks.Open (fPath & fName)
'Windows(fName).Activate
Windows(fName).Activate
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 4"
Range("A1:BB2000").Select
Selection.Copy
Windows("CREATEXMLTEST.xlsm").Activate
Sheets("in").Select
Range("A1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 5"
'Application.Wait waitTime
'MsgBox fPath & "____" & fName & "--" & Cnt & " MSSG 6"
Selection.Copy
Windows("CREATEXMLTEST.xlsm").Activate
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 7"
ActiveCell.Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
been running a macro for quite some time in excel 2007 suddenly it has now developed a problem without it being amended in anyway, keep getting errors at various lines many involving subscript errors. I know activate is not the best command in the vba world to use but I am not a vba pro. selction of the code is below, any help most appreciated as I'm getting grief about this from several people.
Sub TEST3()
'Sub AUTO_OPEN()
Dim fPath As String
Dim fName As String
Dim xname As String
Dim Cnt As Long
Dim WB As Workbook
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
'PATH TO WHERE THE EXCEL FILES ARE HELD
fPath = "\\HAWKEYE\Company\DAVEM\WALES\"
fName = Dir(fPath & "*.xls")
xname = Replace(fName, ".xls", "")
Cnt = 0
'MsgBox "THE NAME OF THE ACTIVE DIRECTORY IS " & fPath & " MSSG 1"
MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & fName & " MSSG 2"
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 3"
While Len(fName) > 0
Workbooks.Open (fPath & fName)
'Windows(fName).Activate
Windows(fName).Activate
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 4"
Range("A1:BB2000").Select
Selection.Copy
Windows("CREATEXMLTEST.xlsm").Activate
Sheets("in").Select
Range("A1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 5"
'Application.Wait waitTime
'MsgBox fPath & "____" & fName & "--" & Cnt & " MSSG 6"
Selection.Copy
Windows("CREATEXMLTEST.xlsm").Activate
'MsgBox "THE NAME OF THE ACTIVE WORKBOOK IS " & ActiveWorkbook.Name & " MSSG 7"
ActiveCell.Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False