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.

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 wrote:
>
> 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

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

"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
>