starsky
09-07-2009, 06:53 AM
Hi,
Got a very strange problem running a macro. The code below copies data (from row 12 down) from any workbook in a specified folder, activates the workbook called "Absence Master template" and pastes it there.
The problem occurs when a colleague tries to run it from his pc. It works fine when I log in to his pc, or either of us logs in to mine (we've not yet tested on any others).
No error is produced, but instead of pasting to the Absence Master template workbook, the data is copied back to it's original workbook. It appears the line to activate the correct destination is being missed.
Sub CollateAbsence()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "S:\Location\Folder"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
'DO YOUR CODE HERE
ActiveWindow.FreezePanes = False
Columns.Hidden = False
Range("A12:I100", ActiveCell.SpecialCells(xlLastCell)).Copy
Workbooks("Absence Master template").Sheets("Data").Activate
Range("A2").Select
If Range("A2") = "" Then
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
With ActiveCell
Cells(.Row + 1, .Column).Select
End With
ActiveSheet.Paste
End If
'End of your code
Cells.Font.Size = 8
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
How can this be???
Got a very strange problem running a macro. The code below copies data (from row 12 down) from any workbook in a specified folder, activates the workbook called "Absence Master template" and pastes it there.
The problem occurs when a colleague tries to run it from his pc. It works fine when I log in to his pc, or either of us logs in to mine (we've not yet tested on any others).
No error is produced, but instead of pasting to the Absence Master template workbook, the data is copied back to it's original workbook. It appears the line to activate the correct destination is being missed.
Sub CollateAbsence()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "S:\Location\Folder"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
'DO YOUR CODE HERE
ActiveWindow.FreezePanes = False
Columns.Hidden = False
Range("A12:I100", ActiveCell.SpecialCells(xlLastCell)).Copy
Workbooks("Absence Master template").Sheets("Data").Activate
Range("A2").Select
If Range("A2") = "" Then
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
With ActiveCell
Cells(.Row + 1, .Column).Select
End With
ActiveSheet.Paste
End If
'End of your code
Cells.Font.Size = 8
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
How can this be???