Excel activate in excel vba

Joined
Nov 29, 2013
Messages
1
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top