Excel 2000 VBA: Formatting data to new layout.

[Paul91S]

I have the following VBA Macro:

Sub rtyrty()
Dim J&, k&, n&, sCrit$
Application.ScreenUpdating = 0
If ActiveSheet.FilterMode = False Then MsgBox "Filtered data is not found", 64: Exit Sub
sCrit = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2)
If MsgBox("Create a book " & sCrit, vbQuestion + vbYesNo) = vbNo Then Exit Sub
[a1].CurrentRegion.Copy tempSh.Cells(1, 1)
With tempSh.[a1].CurrentRegion.Columns(16).Resize(, 5)
J = .Rows.Count
For k = 1 To 5
n = k * (J + 1) + 1: .Columns(k).Copy tempSh.Cells(n, 1)
Next k
.Delete Shift:=xlToLeft
End With
With tempSh: .Name = sCrit: .Copy: .UsedRange.Clear: End With
On Error Resume Next
With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path & "\" & sCrit & ".xls", FileFormat:= _
xlWorkbookNormal, CreateBackup:=False
.Close 0
End With
Application.ScreenUpdating = -1
End Sub


Every 3 months, people enter in feedback through an intranet form and submit it. My manager then receives this data from IM in a basic data list.

What this macro does is copies a persons filtered data from the IM work book and pastes it into a new work book. With this it then moves some of the columns of data underneath the other columns so that the data has a better appearance and is fully readable. This works fine.

The problem : if a cell that is copied has more than 256 characters entered, when this cell is pasted it chops off the text at 256 characters and information is lost.

Is there any way of fixing this??

Many thanks

[Paul91S]

1-2-1 Master Spreadsheet.xls

Attached is a mock up of the spreadsheet as it appears from IM. Filter by someones name and click "Create Report" this starts the macro.