Putting Adressen op State-Specific Werkbladen

Linda heeft een werkblad met 250 adressen uit het hele land. Ze wil de adressen te scheiden, door de staat, om verschillende werkbladen zodat elk werkblad bevat adressen alleen vanuit een bepaalde toestand.

Er is geen manier om dit te doen met een van de ingebouwde Excel functies of tovenaars. Dit is waarschijnlijk een weerspiegeling van het feit dat de meeste mensen verlaten de gegevens op één werkblad en vervolgens diverse Excel instrumenten (zoals verfijning) slechts een subset van de totale gegevens weer.

Als u wilt state-specifieke informatie aan afzonderlijke vellen te kopiëren, maar u kunt dit handmatig doen met behulp van AutoFilter. Dit werkt vooral goed als je alleen maar een paar staten in uw bronnen van gegevens. Alleen voor een AutoFilter en alleen die rijen die zijn in de staat die u wilt kopiëren. Selecteer de zichtbare rijen, kopieer ze en plak ze aan een nieuw werkblad. Herhaal het proces met elk van de andere staten in de oorspronkelijke dataset.

Als u gegevens voor een flink aantal platen hebben, kunt u het kopiëren door het automatiseren van dit proces. De volgende macro zal de AutoFilter mogelijkheden van Excel gebruiken om de informatie te kopiëren naar een nieuw werkblad. Het doet dit voor elke unieke waarde in de kolom staat, die wordt bepaald door de iCol variabele. (In dit voorbeeld iCol ingesteld op 5, waardoor de staten in kolom E)

Sub NewSheetEachAutofilter ()
Dim Wori Zoals werkblad
Dim wkn Zoals werkblad
Dim Wpt Zoals werkblad
Dim bAutoFilter zoals Van Boole
Dim PT Als draaitabel
Dim RPT als Waaier
Dim rCell als Waaier
Dim iCol As Integer
Dim sHeader als Koord

On Error GoTo Errhandler
Application.ScreenUpdating = False
iCol = 5 'Filter allemaal op Col E

Stel Wori = ActiveSheet
Met Wori
'Save Autofilter-status
bAutoFilter = .AutoFilterMode
Als Niet bAutoFilter Dan 'inschakelt autofilter
.Range ("A1"). AutoFilter
End If
Als .FilterMode Dan .ShowAllData

'Gebruik maken van een draaitabel op een temp
'Blad aan een unieke lijst te krijgen
Stel PT = .PivotTableWizard _
(SourceType: = xlDatabase, _
SourceData:.. = Range ("a1") CurrentRegion, _
TableDestination: = "", _
TableName: = "PivotTable1")
sHeader = .Cells (1, iCol)
Met PT
.AddFields RowFields: = sHeader
.PivotFields (SHeader) .Orientation = xlDataField
.ColumnGrand = False
End With
Stel Wpt = ActiveSheet
Met Wpt
Set RPT = .Range (.Range ("A3"), _
.Cells (.Cells.Rows.Count, 1) .End (xlUp))
End With

'Lus door unieke lijst
Voor Elke rCell In RPT
.Range ("A1") AutoFilter Field:. = ICol, _
Criteria1: = rCell.Value
'Het creëren van nieuwe blad en noem maar op met de staat
Stel wkn = Worksheets.Add
wks.Name = rCell.Value
.AutoFilter.Range.Copy Wks.Range ("A1")
Volgende
.ShowAllData
.Selecteer
End With
Application.DisplayAlerts = False
wPT.Delete

ExitHandler:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Verwijder filter als er geen vorige
Als Niet bAutoFilter Dan
wOri.AutoFilterMode = False
End If
Stel rCell = Nothing
Set RPT = Nothing
Stel PT = Nothing
Stel Wori = Nothing
Stel wkn = Nothing
Stel Wpt = Nothing
Exit Sub

Errhandler:
MsgBox Err.Number & ":" & Err.Description
Hervatten ExitHandler
End Sub

De code kan complex uitzien vanwege de lengte, maar het is niet bijzonder moeilijk. Het zorgt ervoor dat de AutoFilter is ingeschakeld, en dan ontstaat er een draaitabel op basis van uw oorspronkelijke gegevens. Dit Draaitabel wordt gebruikt om de lijst van staten te verzamelen van de gegevens. Elke staat wordt vervolgens op de originele gegevens als filtercriteria. De gefilterde informatie wordt vervolgens gekopieerd naar een nieuw werkblad met de naam met behulp van de staat.

De macro niet de oorspronkelijke gegevens te wijzigen. Als u liever heeft dat de oorspronkelijke gegevens verwijderd nadat het tot een werkblad wordt verplaatst, dan alles wat je hoeft te doen is het toevoegen van een enkele regel code. Voeg deze regel direct na de regel dat wist de draaitabel (wPT.Delete):

wOri.Delete

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (3383) is van toepassing op Microsoft Excel 97, 2000, 2002 en 2003. U kunt een versie van deze tip voor de ribbon-interface van Excel (Excel 2007 en later) vindt u hier: Putting Adressen op State-Specific Werkbladen.


© 2019 Quilcedacarvers.com | Contact us: webmaster# quilcedacarvers.com