P
prkhan56
Hello All,
I am using Windows XP/Office 2003 and have the following problem
I have downloaded a file from Debra Dalgleish's Web Site www.contexture.com(File
Name:AdvFilterCity.Zip) and tried to change it to suit my needs.
But I have few problems with it...as shown in the JPG files viz. Before
and After.
(I have made JPG files)
The File name "Before" shows the actual entry and the File name
"After" shows when the macro FilterCities is run.
As can be seen in the file "After" in Columns F & G ...same data is
repeated from columns C & D. This is what is going wrong.
When the macro FilterCities is run I need to have the entire row
(Columns A to H) copied one below other as shown in File "Before"
Following is the macro
Option Explicit
Sub FilterCities()
Dim myCell As Range
Dim wks As Worksheet
Dim DataBaseWks As Worksheet
Dim ListRange As Range
Dim dummyRng As Range
Dim myDatabase As Range
Dim TempWks As Worksheet
Dim rsp As Integer
Dim i As Long
'include bottom most header row
Const TopLeftCellOfDataBase As String = "A4"
'what column has your key values
Const KeyColumn As String = "A"
'where's your data
Set DataBaseWks = Worksheets("Main")
i = DataBaseWks.Range(TopLeftCellOfDataBase).Row - 1
rsp = MsgBox("Include headings?", vbYesNo, "Headings")
Set TempWks = Worksheets.Add
With DataBaseWks
Set dummyRng = .UsedRange
Set myDatabase = .Range(TopLeftCellOfDataBase, _
.Cells.SpecialCells(xlCellTypeLastCell))
End With
'rebuild the List
With DataBaseWks
Intersect(myDatabase, .Columns(KeyColumn)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), _
Unique:=True
'Add the heading to the criteria area
TempWks.Range("D1").Value = _
.Cells(.Range(TopLeftCellOfDataBase).Row, KeyColumn).Value
End With
With TempWks
Set ListRange = .Range("a2", .Cells(.Rows.Count,
"A").End(xlUp))
End With
With ListRange
.Sort Key1:=.Cells(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
'check for individual City worksheets
For Each myCell In ListRange.Cells
If WksExists(myCell.Value) = False Then
Set wks = Sheets.Add
On Error Resume Next
wks.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Please rename: " & wks.Name
Err.Clear
End If
On Error GoTo 0
wks.Move after:=Sheets(Sheets.Count)
Else
Set wks = Worksheets(myCell.Value)
wks.Cells.Clear
End If
If rsp = 6 Then
DataBaseWks.Rows("1:" & i).Copy Destination:=wks.Range("A1")
End If
'change the criteria in the Criteria range
TempWks.Range("D2").Value = "=" & Chr(34) & "=" & myCell.Value
& Chr(34)
'transfer data to individual City worksheets
If rsp = 6 Then
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1
2"), _
CopyToRange:=wks.Range("A1").Offset(i, 0), _
Unique:=False
Else
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D12"), _
CopyToRange:=wks.Range("A1"), _
Unique:=False
End If
Next myCell
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
MsgBox "Data has been sent"
Call SetColumnWidth
Sheets("MAIN").Select
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
I can mail the JPG files to someone who wish to help me out
Can anybody help me out please?
TIA
Rashid Khan
I am using Windows XP/Office 2003 and have the following problem
I have downloaded a file from Debra Dalgleish's Web Site www.contexture.com(File
Name:AdvFilterCity.Zip) and tried to change it to suit my needs.
But I have few problems with it...as shown in the JPG files viz. Before
and After.
(I have made JPG files)
The File name "Before" shows the actual entry and the File name
"After" shows when the macro FilterCities is run.
As can be seen in the file "After" in Columns F & G ...same data is
repeated from columns C & D. This is what is going wrong.
When the macro FilterCities is run I need to have the entire row
(Columns A to H) copied one below other as shown in File "Before"
Following is the macro
Option Explicit
Sub FilterCities()
Dim myCell As Range
Dim wks As Worksheet
Dim DataBaseWks As Worksheet
Dim ListRange As Range
Dim dummyRng As Range
Dim myDatabase As Range
Dim TempWks As Worksheet
Dim rsp As Integer
Dim i As Long
'include bottom most header row
Const TopLeftCellOfDataBase As String = "A4"
'what column has your key values
Const KeyColumn As String = "A"
'where's your data
Set DataBaseWks = Worksheets("Main")
i = DataBaseWks.Range(TopLeftCellOfDataBase).Row - 1
rsp = MsgBox("Include headings?", vbYesNo, "Headings")
Set TempWks = Worksheets.Add
With DataBaseWks
Set dummyRng = .UsedRange
Set myDatabase = .Range(TopLeftCellOfDataBase, _
.Cells.SpecialCells(xlCellTypeLastCell))
End With
'rebuild the List
With DataBaseWks
Intersect(myDatabase, .Columns(KeyColumn)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), _
Unique:=True
'Add the heading to the criteria area
TempWks.Range("D1").Value = _
.Cells(.Range(TopLeftCellOfDataBase).Row, KeyColumn).Value
End With
With TempWks
Set ListRange = .Range("a2", .Cells(.Rows.Count,
"A").End(xlUp))
End With
With ListRange
.Sort Key1:=.Cells(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
'check for individual City worksheets
For Each myCell In ListRange.Cells
If WksExists(myCell.Value) = False Then
Set wks = Sheets.Add
On Error Resume Next
wks.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Please rename: " & wks.Name
Err.Clear
End If
On Error GoTo 0
wks.Move after:=Sheets(Sheets.Count)
Else
Set wks = Worksheets(myCell.Value)
wks.Cells.Clear
End If
If rsp = 6 Then
DataBaseWks.Rows("1:" & i).Copy Destination:=wks.Range("A1")
End If
'change the criteria in the Criteria range
TempWks.Range("D2").Value = "=" & Chr(34) & "=" & myCell.Value
& Chr(34)
'transfer data to individual City worksheets
If rsp = 6 Then
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1
2"), _CopyToRange:=wks.Range("A1").Offset(i, 0), _
Unique:=False
Else
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1
2"), _CopyToRange:=wks.Range("A1"), _
Unique:=False
End If
Next myCell
Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True
MsgBox "Data has been sent"
Call SetColumnWidth
Sheets("MAIN").Select
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
I can mail the JPG files to someone who wish to help me out
Can anybody help me out please?
TIA
Rashid Khan