255 Character Cell Limit From VBA Workaround?

[eddiev1972]

I am trying to pass a string from VBA excel to a cell on a worksheet and am encountering the 255 character limit that is truncating my string. I have found a workaround for some objects at Microsoft, but It does not seem to work on cells.

I was hoping someone could assist me with any suggestions.

When I try the below, string NoteString1 correctly appears in the cell, but NoteString2 does not appear at all.

Below is my current code

   NoteString1 = "Note 1 Here"  'actual note is over 200 characters
   NoteString2 = "Note 2 Here"  'actual note is over 100 characters
                    
   FirstNote = Len(NoteString1) + 1
                    
   Cells((i * Spacer + 6), 4).Characters(Start:=1).Text = NoteString1
   Cells((i * Spacer + 6), 4).Characters(Start:=FirstNote).Text= NoteString2

Thank you for any assistance.

 

[eddiev1972]

Thank you! With Andrew Poulsom's additional information, I updated my code to the below, which seems to have worked.

Sheets("PAM").Select
Cells.Copy
Workbooks.Add
Cells.PasteSpecial xlPasteAll
Application.DisplayAlerts = False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.Close

 

[eddiev1972]

Deleted: Double Post

 

[Ten98]

I was searching for days for a workaround for this, and this does exactly what I needed, thanks so much

 

[TechTank]

Look here:

http://support.microsoft.com/kb/213548

Hi Andrew,

Your link was top on my search for an answer for this in Google but I was wondering if there was a way to add this code into the "Worksheet" code module so that when a particular sheet is copied it activates automatically?

Or is it a case of assigning the code to a button like a macro and having a user execute it manually?

Any assistance greatly appreciated.

Mark.

 

[Andrew Poulsom]

Sorry, I don't understand.

 

[TechTank]

Sorry,

The code from Microsoft:

    Sub Test()

        'Turn off screen updating while the macro runs.
        Application.ScreenUpdating = False

        'xSheet is the sheet we want to copy.
        Set xSheet = ActiveSheet

        'Copy the worksheet to the destination you want. Cells with more
        'than 255 characters will be truncated by this step.
        xSheet.Copy Before:=Sheets(xSheet.Index)

        'ySheet is the new worksheet.
        Set ySheet = ActiveSheet

        'Copy all of the cells on the original worksheet...
        xSheet.Range("A:IV").Copy

        '...activate the new worksheet...
        ySheet.Range("A1").Select

        '...and paste the copied cells. All cells in the new worksheet
        'now have the correct contents.
        ySheet.Paste

        'Clear out the clipboard and select cell A1.
        Application.CutCopyMode = False
        Range("A1").Select

    End Sub

I assume is meant for a macro to be executed by a user when required?

Is there some way to tweak this code so that it could be added to the code for a particular worksheet (right-click worksheet and select View Code for the VBA window). When a user selects to copy the worksheet the code automatically kicks in, copies the sheet and then copies and pastes the values from the original worksheet to the new one?

 

[Andrew Poulsom]

Sorry, there isn't an event that fires when a worksheet is copied.

 

[TechTank]

No worries but thank you for picking this up. I'll assign it to a button and work from there.

Thanks again Andrew.

Mark.