Change macro range from column A to B

[sachin483]

I want to change the column select range from A to B with top 5 rows freeze in every sheet and then split

Sub sheets_seperator()
Dim flpath As String
Dim lr As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False

cnt = 1
flpath = Application.InputBox("Enter the file location") & "\"

On Error Resume Next
On Error Resume Next
Sheets("List").Delete

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "List"

Sheets("Rep_1").Select
Columns("a:a").Copy

Sheets("List").Select
Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

Range("A:A").RemoveDuplicates 1, xlYes
Range("A1").Select
Range("A1:A2").EntireRow.Delete
Columns.AutoFit
ActiveSheet.UsedRange.AutoFilter 1, "*Total*"
Sheets("List").AutoFilter.Range.Offset(1).Delete xlShiftUp
ActiveSheet.ShowAllData

Set mstrwb = ThisWorkbook

lr = Sheets("List").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
Teamname = Cells(i, 1).Value

Workbooks.Add
ActiveWorkbook.SaveAs flpath & Teamname & "- Report" & ".xlsx", FileFormat:=51
Set extr = ActiveWorkbook

For Each ws In mstrwb.Worksheets
ws.Copy After:=extr.Sheets(Sheets.Count)
Next ws

Sheets("List").Delete
Sheets(1).Delete

For Each ws In extr.Sheets
ws.Select

Range("a4").Select
Rows("4:4").Select
       Selection.AutoFilter
Selection.AutoFilter 1, "<>" & "*" & Teamname & "*"

Range("a4").Select
ActiveSheet.AutoFilter.Range.Offset(1).Delete xlShiftUp
ActiveSheet.ShowAllData
Range("a5").Select
Next ws

Sheets(2).Select

ActiveWorkbook.Close True
cnt = cnt + 1
Next i
MsgBox cnt - 1 & " Files created Successfully"

End Sub

 

[bebo021999]

Not sure what the code is doing, but try to replace A with B within code?
Range("A1")
Range("A1:A2")
Range("A:A")
Range("A1")
columns("a:a")

to become

Range("B1")
Range("B1:B2")
Range("B:B")
Range("B1")
columns("B:B")

???

 

[sachin483]

tried but not file not splitting, the code is trying split various sheet from workbook where top 5 rows are freeze and range is column B

 

[Alex Blakenburg]

I assume the Team Names are in column A of the List sheet. What column are they in, in all the other sheets ? Show us a sample including the row and column references..

 

[sachin483]

SL.	Div	COD	DSCR	COD	DSCR	No.	CL	GS	TGT
No.						M		SL
1	AA	AR00001	NNNN	A1	B1	5	A	94008510	87717213
2	AA	AR00001	NNNN	A2	B2	1	C	57286509	56432346
3	AA	AR00001	NNNN	A3	B3	2	A	63468724	62689320
4	AA	AR00001	NNNN	A4	B4	1	A	58943031	58231437
5	BB	AR00002	BBBB	B1	A1	1	A	58753766	58299423
6	BB	AR00002	BBBB	B2	A2	1	B	57579210	57497094
7	BB	AR00002	BBBB	B3	A3	1	B	57315937	57373917
8	BB	AR00002	BBBB	B4	A4	6	B	75290243	76465857

sample of sheet1 ,rows and column will be freeze

 

[Alex Blakenburg]

What is the macro doing and what is it not doing ?
Is it creating the separate workbooks but not deleting the rows that don't meet the criteria ?
What CELL is SL in ?
What column are you wanting to filter on is it the "DIV" column ?

 

[sachin483]

macro is creating separate workbooks
wants to filter of div column
top 5 rows should be freeze with formatting

previously div column was in A column now i have to shifted to B so macro is not creating separate workbooks

 

[Alex Blakenburg]

The Div column is clearly in Column B of the other worksheets but what column is it in on the sheets List AND Rep_1 ?
Also you didn't answer what cell the heading SL is in ?

 

[sachin483]

Div Column sheets List AND Rep_1 - column B in every sheet
cell the heading SL is in - will be column A of every sheet

 

[Alex Blakenburg]

What row is SL in ?