Hi,

I have a requirement here. Below is my code where it will combine 6 sheets in to one sheet i.e combined sheet and converts one column text
column based fixed width text data to coulmns.

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
Sheets(1).Select
Sheets(1).Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(20, 2), Array(40, 2), Array(74, 2), Array(108, 2), _
Array(142, 2), Array(162, 2)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit

In the above code I need two more requirements. i.e.
1) Everytime before I am executing combined macro, I am deleting combined sheet as it is giving some error if I dont delete. Can some one give some code how to overwrite the combined sheet if it exists.

2) my second requirement inthe combined sheet I need a column of sheet name of particular that row exists in which sheet.

Can anyone resolve my query