Table showing #### when copied

[ChrisM92]

Hi,

I've got a macro to generate emails that includes copying data from my worksheet into the email body. Never had any issues with it until the report I'm currently working on, and now for some reason one of the columns keeps getting pasted as #### instead of the value??

On my spreadsheet it all shows fine, no issues with the formatting, so it's happening when being copied, and only with the one column..
I tried adding "Cells.EntireColumn.AutoFit" for when it copies into a temp file, but even that didn't fix it

The macro references a table which contains all my contact details - this is the "ws" referenced throughout

Any ideas why this column is getting squashed?

SheetName = ws.Range("a" & i).Value
SheetNameColumns = Sheets(SheetName).Cells(1, Columns.Count).End(xlToLeft).Column
SheetNameRows = Sheets(SheetName).Range("a" & .Rows.Count).End(xlUp).Row
ColumnLetter = Split(Cells(1, SheetNameColumns).Address, "$")(1)


ws.Range("h" & i) = "A1" & ":" & ColumnLetter & SheetNameRows


t = ws.Range("h" & i).Value
            
Set rng = Nothing

On Error Resume Next

Set rng = Sheets(SheetName).Range(t).SpecialCells(xlCellTypeVisible)

On Error GoTo 0
       
            With OutMail
                .To = ws.Range("b" & i).Value
                .CC = ws.Range("c" & i).Value
                .Subject = ws.Range("d" & i).Value
                .HTMLBody = ws.Range("f" & i).Value & RangetoHTML2(rng)
                .Save
            End With


        Next i
    End With

Function RangetoHTML2(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook


TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
    .Range("A1").PasteSpecial Paste:=8
    .Range("A1").PasteSpecial xlPasteValues, , False, False
    .Range("A1").PasteSpecial xlPasteFormats, , False, False
    .Range("A1").Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
End With


'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
     SourceType:=xlSourceRange, _
     Filename:=TempFile, _
     Sheet:=TempWB.Sheets(1).Name, _
     Source:=TempWB.Sheets(1).UsedRange.Address, _
     HtmlType:=xlHtmlStatic)
    .Publish (True)
End With


'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.readall
ts.Close
RangetoHTML2 = Replace(RangetoHTML2, "align=center x:publishsource=", _
                      "align=left x:publishsource=")


'Close TempWB
TempWB.Close savechanges:=False


'Delete the htm file we used in this function
Kill TempFile


Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

 

[yky]

What do you see on the address bar if you click any one of those #### cells? The address bar will show you the value stored in that cell regardless of the format.

If you manually widen the column, say, double the width, what will you see?