reflection in excel
-
- StarLounger
- Posts: 77
- Joined: 15 Jan 2011, 02:32
reflection in excel
Sir, in the attached file, certain field names are there in sheet1 like sector,bran,br_name, status. What I would like to do is to reflect an sheet2 all the bran which comes under their respective sector code. In status field "S" stands for Sector.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: reflection in excel
Should the code create the list of sectors in Sheet2 or can it use the existing list of branches in Sheet2?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 77
- Joined: 15 Jan 2011, 02:32
Re: reflection in excel
Sir, its better if the sector code automatically comes at sheet2 and thereafter the bran codes ...
Regards
Jigyansha
Regards
Jigyansha
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: reflection in excel
Try this; the macro should be run from Sheet1.
Code: Select all
Sub ListBranches()
Dim wsh1 As Worksheet
Dim wsh2 As Worksheet
Dim r1 As Long
Dim r2 As Long
Dim m1 As Long
Dim m2 As Long
Dim t As Long
Dim c As Long
Dim strBranch As String
Application.ScreenUpdating = False
Set wsh1 = ActiveSheet
' Create target sheet
Set wsh2 = Worksheets.Add
wsh1.Range("A1:E1").Copy wsh2.Range("A1")
For c = 1 To 12
wsh2.Cells(1, c + 5) = "p" & c
Next c
r2 = 1
m1 = wsh1.Cells(wsh1.Rows.Count, 1).End(xlUp).Row
For r1 = 2 To m1
If wsh1.Cells(r1, 5) = "S" Then
r2 = r2 + 1
For c = 1 To 5
wsh2.Cells(r2, c) = wsh1.Cells(r1, c)
Next c
c = 5
End If
c = c + 1
wsh2.Cells(r2, c) = wsh1.Cells(r1, 3)
Next r1
wsh2.Columns.AutoFit
wsh2.Columns(1).Hidden = True
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 77
- Joined: 15 Jan 2011, 02:32
Re: reflection in excel
Thanks a lot Sir, this is what I exactly required ... You are really genius ...