VBA query - Macro to truncate no. chars in cells

[Rags]

Hi

I use the below code to truncate the number of characters in cells on a worksheet. It works well with only one workbook open.

Sub Truncate()
    Application.ScreenUpdating = False

    Dim LastHeader As Long
    Dim Cell As Range
    LastHeader = Range("IV1").End(xlToLeft).Column
    
    For Each Cell In Application.Intersect(Sheet1.UsedRange, Columns("A:AO"))
        Cell.Value = Left(Cell.Value, 80)
    Next Cell

    Application.ScreenUpdating = True

End Sub

My problem is that I need to run it on a worksheet in a different workbook. How can I specify which workbook & sheet? Currently it only works when the macro is within a module in the active workbook.

I've tried various things - adding Activeworkbook, Worksheets("Data") etc to the 'For Each Cell In Application.....' line but nothing has worked yet.

Thanks in advance

Rags

 

[BrianB]

Not a good habit to use Excel reserved words as variables ("Cell") so I have changed that.

** UNTESTED**

Sub Truncate()
    Dim LastHeader As Long
    Dim MyCell As Range
    Dim ws As Worksheet
    '----------------------------------------------------
    Set ws = Workbooks("Book1.xls").Worksheets("Sheet1")
    '-----------------------------------------------------
    Application.ScreenUpdating = False
    LastHeader = Range("IV1").End(xlToLeft).Column
    '- loop
    For Each MyCell In Application.Intersect(ws.UsedRange, Columns("A:AO"))
        MyCell.Value = Left(MyCell.Value, 80)
    Next MyCell
    '-
    Application.ScreenUpdating = True
End Sub