Excel sheet copy/paste problems

mingiunate

New Member
Joined
Dec 14, 2005
Messages
4
Hi!

I have a macro, and it is used to export a few worksheets by copy/pasting them into a new workbook..

However, some of the cells in the sheet i am exporting have a lot of text in them, and in the copies, the text is being cut off...


is there anythign i can do short of splitting the cells up?
can i change the format of the cell, or anything?

thanks!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
Since your using a macro, why not make the macro format the text to autowidth after it pastes it?
 

mingiunate

New Member
Joined
Dec 14, 2005
Messages
4
huh?
i have no clue what that means...


but this has nothing to do with column width...
its only copying the first 255 characters of the cells in the sheets that get copy/pasted over


thanks!
 

mingiunate

New Member
Joined
Dec 14, 2005
Messages
4

ADVERTISEMENT

Private Sub cmdRenewalPackage_Click()
Dim sName As String
Dim OLEit As OLEObject

If MsgBox("Would you like to export the Financial Package?", vbYesNo, "Export?") = vbYes Then
sName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xls),*.xls", _
Title:="Specify name and location of where to save", InitialFileName:="Financial Package.xls")
If sName = "False" Then
MsgBox "Renewal Package has not been saved.", , "Warning!"
Exit Sub
End If
Sheets(Array("Cover Page", "Table of Contents", "Summary of Cost", "Continuation Rates", _
"Summary of Cost - Billing", "Continuation Rates - Billing", "Policy Assumptions")).Select
Sheets("Cover Page").Activate
Sheets(Array("Cover Page", "Table of Contents", "Summary of Cost", "Continuation Rates", _
"Summary of Cost - Billing", "Continuation Rates - Billing", "Policy Assumptions")).Copy
sName = Replace(sName, ".xls", " - Financial Package.xls", , , vbTextCompare)
For Each OLEit In ActiveWorkbook.ActiveSheet.OLEObjects
If InStr(1, OLEit.progID, "CommandButton") <> 0 Then
OLEit.Delete
End If
Next OLEit

ActiveWorkbook.SaveAs Filename:=sName
ActiveWorkbook.Close
MsgBox "Financial Package has been saved.", , "Saved"
End If
End Sub
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
OK i'm no expert, so I'm giving this the old college try, but if anyone else has any ideas I'd love to hear them
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
It copied mine just fine, let me add a few lines of data to see what happens

entering I did a len count and it takes a cell full of 32676 or whatever the number is and changes it to 255?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,955
Members
412,300
Latest member
Chaneycr
Top