problem with the Sheet name, urgent help required

[Amit1]

Dear Friends,
I am using a macro for formatting and sorting (based on a particular column value) for more than 1000 of files (wbk). The problem is i have to rename each time i run the macro in the code for the renamed "sheet1".Kindly suggest some way where i do not have to rename it every time.The code is provided below for reference:
Sub format1()
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Heading1"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Heading2"
Range("C1").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "Heading3"
Columns("D:D").Select
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
ActiveCell.FormulaR1C1 = "Heading4"
Columns("E:E").Select
Columns("D:D").EntireColumn.AutoFit
Range("E1").Select
ActiveCell.FormulaR1C1 = "Heading5"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Heading6"
Columns("G:G").Select
Columns("F:F").EntireColumn.AutoFit
Range("G1").Select
ActiveCell.FormulaR1C1 = "Heading7"
Columns("H:H").Select
Columns("G:G").EntireColumn.AutoFit
Range("H1").Select
ActiveCell.FormulaR1C1 = "Heading8"
Columns("I:I").Select
Columns("H:H").EntireColumn.AutoFit
Range("I1").Select
ActiveCell.FormulaR1C1 = "Heading9"
Columns("J:J").Select
Columns("I:I").EntireColumn.AutoFit
Selection.Delete Shift:=xlToLeft
Range("J1").Select
ActiveCell.FormulaR1C1 = "Heading10"
Columns("K:K").Select
Columns("J:J").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
Columns("K:CE").Select
Selection.ClearContents
ActiveWorkbook.Save
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("K1").Select
Range("A1:J200").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear (This "Sheet1" should be renamed every time)
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C200") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:J200")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Save
End Sub

Looking forward for your help

 

[vds1]

what would you rename the sheet every time ?

 

[andrewkard]

Hello, maybe this can help:

ActiveWorkbook.Worksheets("Sheet1").Name = "Sheet4"

or this

ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear

 

[Misca]

This should do about the same as your original macro without having to rename the sheet:

Sub MicroFormat()
Dim Rng As Range
Columns("A:A").Delete shift:=xlToLeft
Rows("1:1").Delete shift:=xlUp
Rows("1:1").Insert shift:=xlDown
Range("J:J").Delete shift:=xlToRight
With Range("A1:J1")
    .Formula = "=""Heading ""&Column(A1)"
    .Value = .Value
    .EntireColumn.AutoFit
End With
Columns("K:CE").ClearContents
Set Rng = Range("A1").CurrentRegion
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Intersect(Rng, Range("C:C")), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal
    .SetRange Rng
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveWorkbook.Save
End Sub

 

[Amit1]

Dear Friends,
Thanks a lot for your support, now the code is working fine and am able to gather the output.
Thank you all once again.
Best Regards,
Amit