Excel Merge Text files

Dear Forum Members,
As I am not a VB expert I have copied some codes and edited to suit for my purpose. My idea is to combine many .pyc files in a folder into one excel sheet. (.pyc files are output files from a lab machine, which is rather like a .txt file). My codes are working sometimes but sometimes not (the file has two Sheets, named "Sheet1" and "AllData"). When debug it gives a "Application-defined or object-defined error" at line "ActiveSheet.Name = g". I request your help to solve this issue. As mentioned I didn't have knowledge in working, please give an explanation. Regards, Pramod

The codes used are here below:

Sub pycfiles()
Application.ScreenUpdating = False
Dim z As Long, e As Long
Dim g As String
Dim strFolder As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.Title = "Select the folder into which the documents will be saved."
If .Show = -1 Then
strFolder = .SelectedItems(1) & "\"
Else
MsgBox "The documents will be saved in the default document file location."
strFolder = ""
End If
End With
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.pyc")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
g = Left(Sheets("Sheet1").Cells(e, 1), Len(Sheets("Sheet1").Cells(e, 1)) - 4)
Worksheets.Add
ActiveSheet.Name = g
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Sheets("Sheet1").Cells(1, 2) & Sheets("Sheet1").Cells(e, 1), _
Destination:=Sheets(g).Range("A1"))
.Name = "deep"
.FieldNames = True
.RowNumbers = False
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("B3:B25").Select
Application.CutCopyMode = False
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Next e
Dim wrk As Workbook
Dim sht As Worksheet
Dim trg As Worksheet
Dim rng As Range
Dim colCount As Integer
Set wrk = ActiveWorkbook
Set trg = wrk.Worksheets("AllData")
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
For Each sht In wrk.Worksheets
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
Next sht
trg.Columns.AutoFit
Sheets("AllData").Select

Range("W:X,O:S,G:K,E:E").Select
Range("E1").Activate
Selection.Delete Shift:=xlToLeft
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("C2").Select
ActiveWorkbook.Worksheets("AllData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AllData").Sort.SortFields.Add Key:=Range("C2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AllData").Sort
.SetRange Range("A2:K7000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("L2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-3]"
Range("A2").Select
'Populate Formulas
On Error Resume Next
lMaxRows = 0
lStartRow = 0
lMaxRows = Sheets("AllData").Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lStartRow = Sheets("AllData").Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
On Error GoTo 0
If (lMaxRows - lStartRow > 0) Then
Sheets("AllData").Select
Range("L2:L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lMaxRows + 0), Type:=xlFillDefault
End If
Range("A1").Select
Application.ScreenUpdating = True
oldworkbook$ = ActiveWorkbook.Name
Dim wb As Workbook
Sheets(Array("AllData", "Sheet1")).Select
Sheets("AllData").Activate
Sheets(Array("AllData")).Copy
Set wb = ActiveWorkbook
If wb.Saved = False Then
Select Case MsgBox("Save this File(GV-Sheet) in Current Well's Folder by Giveing Proper Name.", vbYesNo Or vbExclamation Or vbDefaultButton1, "J & R Solutions")
Case vbYes
wb.Close True
Case vbNo
wb.Close False
End Select
End If
Workbooks(oldworkbook$).Activate
ThisWorkbook.Close SaveChanges:=False
End Sub