Need help adjusting my code:

[HouseofRahl]

Hi all,

Here is what I am trying to do, and what the result is:

I have a varying number of excel files in a folder. I need to make
each sheet in each file a sheet in a new file, so that I have one file
which has all of the info from all of the other files in this folder.

I have the following code, which does what I need, with one slight
problem. Any cells with text that are merged in the original sheets
does not copy over correctly.

For example, lets say I had a merged cell with 26 words in it, in the
newly created sheet, the words would cut off after say, the 11th word,
with the rest of the text just missing.

Here is the code I am using. (Code gotten from multiple sources, but
is not my own.)

Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "" -- Here I just have the path to
said folder
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Is there a command I could put in here to make sure that the formatting
and full text copy over?


I appreciate any help anyone can give.

 

[STEVE BELL]

Worked fine for me in Excel 2000.

had to make sure that following was a single line so added the line
continuation _
WS.Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

WS.Copy _
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

--
steveB

Remove "AYN" from email to respond
"HouseofRahl" <[email protected]>
wrote in message
news:[email protected]...