Finding String throughout WorkBook, copying values to worksheet

KNKLHEAD

New Member
Joined
Jan 10, 2022
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hi! I have a workbook with multiple worksheets. Each of these worksheets have a SKU number in a random column (yikes!) but the SKU numbers have the same format at least. The format for the SKUs is ??.?????.??? -- Question marks represent a number or letter -- two characters, a period, then five characters, a period, then three characters. Examples: 12.AB123.AYB or UM.15U36.DRM or 12.12345.123, etc. The ultimate goal is the find every SKU in the workbook and list them. Thank you!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Welcome to the MrExcel board!

Each of these worksheets have a SKU number in a random column
That is written in the singular. Does that mean a sheet will only have one SKU and you want to find what it is?
.. or might there be tens or hundreds or thousands of SKUs in each worksheet?
 
Upvote 0
Welcome to the MrExcel board!


That is written in the singular. Does that mean a sheet will only have one SKU and you want to find what it is?
.. or might there be tens or hundreds or thousands of SKUs in each worksheet?
Excellent, sorry I was not clear. Each worksheet could have many rows (like 100) that have this SKU number. For example, there are like 8 worksheets, and expect a total of about 1000 SKUs throughout the workbook. So nice of you to help! Thank you!
 
Upvote 0
Try this with a copy of your workbook.

VBA Code:
Sub List_SKUs()
  Dim ws As Worksheet
  Dim b As Variant
  Dim rFound As Range
  Dim FirstAddr As String
  Dim k As Long
  
  ReDim b(1 To Rows.Count, 1 To 1)
  For Each ws In Worksheets
    With ws.UsedRange
      Set rFound = .Find(What:="??.?????.???", LookIn:=xlValues, LookAt:=xlWhole)
      If Not rFound Is Nothing Then
        FirstAddr = rFound.Address
        Do
          k = k + 1
          b(k, 1) = rFound.Value
          Set rFound = .Find(What:="??.?????.???", After:=rFound, LookIn:=xlValues, LookAt:=xlWhole)
        Loop Until rFound.Address = FirstAddr
      End If
    End With
  Next ws
  With Worksheets.Add(After:=Sheets(Sheets.Count))
    .Name = "SKU List"
    .Range("A1").Resize(k).Value = b
  End With
End Sub
 
Upvote 0
Solution
Try this with a copy of your workbook.

VBA Code:
Sub List_SKUs()
  Dim ws As Worksheet
  Dim b As Variant
  Dim rFound As Range
  Dim FirstAddr As String
  Dim k As Long
 
  ReDim b(1 To Rows.Count, 1 To 1)
  For Each ws In Worksheets
    With ws.UsedRange
      Set rFound = .Find(What:="??.?????.???", LookIn:=xlValues, LookAt:=xlWhole)
      If Not rFound Is Nothing Then
        FirstAddr = rFound.Address
        Do
          k = k + 1
          b(k, 1) = rFound.Value
          Set rFound = .Find(What:="??.?????.???", After:=rFound, LookIn:=xlValues, LookAt:=xlWhole)
        Loop Until rFound.Address = FirstAddr
      End If
    End With
  Next ws
  With Worksheets.Add(After:=Sheets(Sheets.Count))
    .Name = "SKU List"
    .Range("A1").Resize(k).Value = b
  End With
End Sub

Wow. It works absolutely perfectly. Thank you so much!
 
Upvote 0
You're welcome. Thanks for the confirmation. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top