PDA

View Full Version : Solved: Macro not working correctly for different user



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???

Bob Phillips
09-07-2009, 07:32 AM
Does he have an S drive mapped?

starsky
09-07-2009, 08:07 AM
Yes, it's our shared drive.

starsky
09-08-2009, 04:16 AM
Solved this by adding the .xls extension to the destination workbook.