Copying worksheet into another worksheet

[ecasabuena]

hi guys,

I have an excel file name Product.xls which is generated from an RDL, also i
have created a Conversion Tool, which is another excel file with a macro,
which can manipulate the Products.xls to generate another report, my problem
is, in this Products.xls, there is one column named Summary which contains
more than 911 characters, when i used the Conversion Tool, it generated
another excel file, but the Summay colum contains only 255 characters.

this is a line of code from my Conversion Tool, which copy the worksheet of
Products.xls into another excel file.

Public Sub ExportDataToOutPut()
Dim ws As Worksheet, wsNew As Workbook
Dim FileName As String, I As Integer

Set wsNew = Workbooks.Add
FileName = wsNew.FullName

I = 1
For Each Worksheet In ThisWorkbook.Worksheets
Windows("Products.xls").Activate
Sheets(Worksheet.Name).Select
Sheets(Worksheet.Name).Copy Before:=Workbooks(FileName).Sheets(I)
I = I + 1
Next

please help me on how am i going to fix my problem.
your reply is highly appreciated.

thanks in advace.

 

[Dave Peterson]

If you manually copy a sheet that has a cell that has more than 255, you'll see
a warning prompt.

In code, you don't see that prompt.

One way around it is to copy the sheet, then go back and copy|paste the cells.

I'm assuming that Product.xls is the workbook that holds the code
(ThisWorkbook).

Option Explicit
Public Sub ExportDataToOutPut()
Dim wks As Worksheet
Dim wkbkNew As Workbook

Set wkbkNew = Workbooks.Add(1) 'single sheet
wkbkNew.Worksheets(1).Name = "delete me later"

For Each wks In ThisWorkbook.Worksheets
'copy the sheet
wks.Copy _
after:=wkbkNew.Worksheets(wkbkNew.Worksheets.Count)

'copy the cells on the sheet
wks.Cells.Copy _
Destination:=wkbkNew.Worksheets _
(wkbkNew.Worksheets.Count).Range("a1")
Next wks

'clean up that first sheet
Application.DisplayAlerts = False
wkbkNew.Worksheets("delete me later").Delete
Application.DisplayAlerts = True

End Sub

 

[ecasabuena]

Thanks Dave, its working smoothly now.
I really appreciate it.
Thank you very much.