AFAIK changes to any sheet is only known at workbook level.

I have not tried this but use the worksheet change event to set a boolean or
a cell on the sheet to indicate change

On each worksheet code add this...., (I used cell A1 but it needs to be
somewhere out of user eyesight or all sheet changes stored on another
sheet!)

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Cells(1, 1) = True
End Sub

Then use your code modified as follows....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim c As Range
For Each wS In Worksheets
If wS.Cells(1, 1) Then
wS.Cells(1, 1) = False
For Each c In wS.UsedRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
End If
Next wS
ThisWorkbook.save
End Sub

--

Regards,
Nigel




"akemeny" wrote in message
...
Some of the workbooks that I use have anywhere between 5-15 spreadsheets,
but
when its open only a few of those will actually be used or have any
changes
made in them. So... Is there a way to adjust the macro below so that it
will
only run in the spreadsheets that had changes made while the workbook was
open?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim c As Range
For Each wS In Worksheets
For Each c In wS.UsedRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
Next wS
ThisWorkbook.Save
End Sub