How to Update Logitech Drivers? : Fast Solution to Error: Automatically filter a table and Copy results to a different sheet in an excel workbook

Automatically filter a table and Copy results to a different sheet in an excel workbook

Hello, I have a large table with about 6 columns and 10,000 rows.
i intend to filter the table using unique IDs which are in the 3rd colum of the table, then copy each result to another excel tab/sheet and then rename that tab with the unique name associated
with that ID (this is in the 4th column).
The problem is that there are about 700 of these unique IDs hence i'll have to filter, copy, and rename 700 times if i have to do this manually!

Is there a macro or an automated way i can achieve this? Here is a sample of the first 3 columns of the table am using.
The unique IDs are in the 3rd column below:

USER_ID
NAME
UNIQUE IDS

01146
MOHAMMED
313

00328
A A
325

01533
AM O
485

01441
ONWU O
319

01738
MARY O
325

01682
A .O
448

01662
A.
N
227

01607
A.A.
522

Thanks.

Anwsers to the Problem Automatically filter a table and Copy results to a different sheet in an excel workbook

Download SmartPCFixer for Free Now

The code below should create the worksheets and copy the data.
However, I have reservations about whether you can actually create 700 worksheets.
I think that you will run out of resources (memory) so you might want to break up your data into manageable
sizes in separate workbooks.

The code relies on the Unique Id's being in column 3 and the names in column 4 used for the worksheet names.

Sub FilterAndCopy()
    Dim wsSource As Worksheet
    Dim wsFiltList As Worksheet
    Dim wsDestin As Worksheet
    Dim rngUnique As Range
    Dim cel As Range
    Dim rngShtName As Range
    Dim wsHyperLinks As Worksheet

    'Edit "Sheet1" to your source data worksheet name
    Set wsSource = Worksheets("Sheet1")

    'Create temporary worksheet for Unique list of ID's
    Set wsFiltList = Sheets.Add(After:=Sheets(Sheets.Count))

    'Create worksheet for Hyperlinks as first worksheet
    Set wsHyperLinks = Sheets.Add(Before:=Sheets(1))
    wsHyperLinks.Name = "HyperLinks"    'Edit "HyperLinks" to sheet name of choice
    wsHyperLinks.Cells(1, 1) = "Links to Sheets"
    wsHyperLinks.Cells(1, 1).Font.Bold = True

    wsFiltList.Cells(1, 1).Name = "UniqueList"

    With wsSource
        'Create a unique list of Id's on separate worksheet
        .Range("C:C").AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=Range("UniqueList"), _
                Unique:=True

        .AutoFilterMode = False     'Start by turning off filtering (if on)
        .UsedRange.AutoFilter       'turn on filtering
    End With

    With wsFiltList
        'Assign unique list of filters to a range variable.
        Set rngUnique = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    End With

    With wsSource

        'Iterate through the Unique list of Id's and _
         set filter and then copy the data to worksheets
        For Each cel In rngUnique
            Set wsDestin = Sheets.Add(After:=Sheets(Sheets.Count))
            .AutoFilter.Range.AutoFilter Field:=3, Criteria1:=cel.Value

            .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy _
                    Destination:=wsDestin.Cells(1, 1)

             'Find the name in column 4 and rename the added worksheet to match
             With .AutoFilter.Range
                Set rngShtName = .Columns(4) _
                        .Offset(1, 0) _
                        .SpecialCells(xlCellTypeVisible).Cells(1, 1)
                wsDestin.Name = rngShtName.Value
             End With

             'Create HyperLink to the worksheet
             With wsHyperLinks
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Formula = _
                    "=HYPERLINK(" & Chr(34) & "[" & ThisWorkbook.Name & "]" & Chr(39) _
                       & wsDestin.Name & Chr(39) & "!A1" & Chr(34) & ", " _
                       & Chr(34) & wsDestin.Name & Chr(34) & ")"
             End With

         Next cel
         .AutoFilterMode = False
    End With

    Application.DisplayAlerts = False
    wsFiltList.Delete   'Temporary sheet with unique list
    Application.DisplayAlerts = True

    ActiveWorkbook.Names("UniqueList").Delete

    wsHyperLinks.Columns("A:A").AutoFit

    'Goto cell A1 of HyperLinks worksheet
    Application.Goto wsHyperLinks.Cells(1, 1)


    Set cel = Nothing
    Set rngUnique = Nothing
    Set wsFiltList = Nothing
    Set wsSource = Nothing
    Set wsDestin = Nothing
    Set rngShtName = Nothing

End Sub

This particular error can easily be resolved with a few simple steps. However, if you are not confident in your ability to use the methods below or they do not fully remove the issue on your computer, you may want to seek the assistance of a qualified repair technician who is well versed in fixing Automatically filter a table and Copy results to a different sheet in an excel workbook.

Back-up the Registry of your operating system.
Find the Registry Key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\OSUpgrade]
Note: this Registry Key should already exist, if it does not exist, create it.
Create a brand new DWORD Value that is named AllowOsUpdate
Set the Value to 0x00000001.
Close and reopen your Control Panel.
Restart the Microsoft Windows.

Recommended Method to Repair the Problem: Automatically filter a table and Copy results to a different sheet in an excel workbook:

How to Fix Automatically filter a table and Copy results to a different sheet in an excel workbook with SmartPCFixer?

1. You can Download SmartPCFixer here. Install it on your system. When you open it, it will perform a scan.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. When the Fixing part is done, your computer has been speeded up and the errors have been fixed

How to Update Logitech Drivers?

Wednesday 6 July 2016

Fast Solution to Error: Automatically filter a table and Copy results to a different sheet in an excel workbook

Automatically filter a table and Copy results to a different sheet in an excel workbook

Anwsers to the Problem Automatically filter a table and Copy results to a different sheet in an excel workbook

Recommended Method to Repair the Problem: Automatically filter a table and Copy results to a different sheet in an excel workbook:

How to Fix Automatically filter a table and Copy results to a different sheet in an excel workbook with SmartPCFixer?

No comments:

Post a Comment