trim down, copy down problems

[slay0r]

Basically I'm trying to add a trim function into a massive report. I know what I want to trim but I'm struggling to get it to work properly with limited knowledge. (I am going on courses for it as soon as they're available!)

My copy down didn't work very well. Is there a function I can change it to so the code isn't 5000 pages long? The data size will change each time so I need to incorporate that instead of having a set range, the columns will always be set in stone though.

Sub changealphaclient()
'
' changealphaclient Macro
' Macro recorded 24/11/2011 by jmartin
'
'
    Range("AT2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-42])"
    Range("AT2").Select
    Selection.AutoFill Destination:=Range("AT2:AT8164"), Type:=xlFillDefault
    Range("AT2:AT8164").Select
    Columns("AT:AT").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-60
    ActiveWindow.LargeScroll Down:=1
    ActiveWindow.ScrollRow = 6031
    ActiveWindow.ScrollRow = 6008
    ActiveWindow.ScrollRow = 5984
    ActiveWindow.ScrollRow = 5949
    ActiveWindow.ScrollRow = 5879
    ActiveWindow.ScrollRow = 5821
    ActiveWindow.ScrollRow = 5739
    ActiveWindow.ScrollRow = 5645
    ActiveWindow.ScrollRow = 5552
    ActiveWindow.ScrollRow = 4453
    ActiveWindow.ScrollRow = 4313
    ActiveWindow.ScrollRow = 4173
    ActiveWindow.ScrollRow = 4021
    ActiveWindow.ScrollRow = 3881
    ActiveWindow.ScrollRow = 3203
    ActiveWindow.ScrollRow = 3051
    ActiveWindow.ScrollRow = 2899
    ActiveWindow.ScrollRow = 2759
    ActiveWindow.ScrollRow = 2595
    ActiveWindow.ScrollRow = 2443
    ActiveWindow.ScrollRow = 2303
    ActiveWindow.ScrollRow = 2151
    ActiveWindow.ScrollRow = 2011
    ActiveWindow.ScrollRow = 1882
    ActiveWindow.ScrollRow = 1742
    ActiveWindow.ScrollRow = 1637
    ActiveWindow.ScrollRow = 1485
    ActiveWindow.ScrollRow = 1357
    ActiveWindow.ScrollRow = 1216
    ActiveWindow.ScrollRow = 1088
    ActiveWindow.ScrollRow = 948
    ActiveWindow.ScrollRow = 842
    ActiveWindow.ScrollRow = 749
    ActiveWindow.ScrollRow = 667
    ActiveWindow.ScrollRow = 609
    ActiveWindow.ScrollRow = 539
    ActiveWindow.ScrollRow = 504
    ActiveWindow.ScrollRow = 457
    ActiveWindow.ScrollRow = 422
    ActiveWindow.ScrollRow = 387
    ActiveWindow.ScrollRow = 352
    ActiveWindow.ScrollRow = 340
    ActiveWindow.ScrollRow = 305
    ActiveWindow.ScrollRow = 281
    ActiveWindow.ScrollRow = 258
    ActiveWindow.ScrollRow = 235
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 1
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-42])"
    Range("AU2").Select
    Selection.AutoFill Destination:=Range("AU2:AU8164")
    Range("AU2:AU8164").Select
    Columns("AT:AU").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("D:D").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Alphaname"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Longname"
    Range("E2").Select
End Sub

 

[jasonb75]

Ok, I'm seeing your logic now, when you run this code it will trim the AR report (if it exists) in every workbook that is currently open.

Public Sub changealphaclient()
    Dim calcVal As Long, wb As Workbook, mCell As Range
With Application
    .ScreenUpdating = False
    calcVal = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
For Each wb In Workbooks
    On Error Resume Next
        If Not wb.Worksheets("AR Report") Is Nothing Then
            With wb.Worksheets("AR Report")
                For Each mCell In .[D:E].SpecialCells(xlConstants, xlTextValues)
                    mCell.Value = Trim(mCell.Value)
                Next
                .[D1] = "Alphaname"
                .[E1] = "Longname"
                .[D:E].EntireColumn.AutoFit
            End With
        End If
    On Error GoTo 0
Next
    .ScreenUpdating = True
    .Calculation = calcVal
    .EnableEvents = True
End With
End Sub

One thing that is ringing alarm bells though,

At this point I'm adding it in it's calling data from another report and adding it to "AR Report" So that's what made me think I need to define AR Report as the sheet it wants to trim.

How is that being done? Is the data being called by formula in the AR Report? If so then we can't use this method, as the formula will be overwritten and will no longer update if the source data is changed.

 

[slay0r]

Nah it's being pulled in from another system by text to columns, I don't think it will affect that, just did a dummy run and it broke on a pivot table later on in the function but that's because the guy before has put spaces after alpha name! I've removed it for today because I'll end up breaking it entirely when It updates all the systems reports I use! I'll have another look at it first thing and try the code you posted, that might fix it too!

Thanks very much for the help and I'll post back tomorrow, hopefully with good news!