Copy adjacent cell value next to blank onto new sheet

[diab0lik]

Hi there,could some one please help me?i need to find all the blanks in one column and copy the adjacent cell onto a new sheet (sheet1)my table looks like this

tracking no	filled date
1234	1/1/20
1235	1/2/20
1236
1237	2/2/20
1238	2/3/20
1239

<tbody>
</tbody>

on separate sheet (sheet2) i would like to get a list of all the unfilled tracking numbers outputted like:

tracking

1236

1239

<tbody>
</tbody>

thanks for any help you can provide

 

[My Aswer Is This]

We always need specific details.
You said:

i need to find all the blanks in one column and copy the adjacent cell onto a new sheet (sheet1)my table looks like this

You did not say what column and what is the adjacent column
And you said:
Copy to a new sheet but did not say where. WE need all sheet names
We need to know what columns

And does the script need to create the new sheet?
If so what name should we give to this new sheet.
​

 

[diab0lik]

Sorry i wasnt clear i have 2 sheets they are just labelled sheet1 and sheet2in sheet1 the "tracking no." column is A and the "filled order" column is B. i want to be able to generate a list on sheet2 that has all the cells in column A (tracking no.) where column B (filled order) is blankno need to create new sheet it is already there. can you see the table?thanks. please let me know if you need more info

We always need specific details.You said:

i need to find all the blanks in one column and copy the adjacent cell onto a new sheet (sheet1) my table looks like thisYou did not say what column and what is the adjacent columnAnd you said:Copy to a new sheet but did not say where. WE need all sheet namesWe need to know what columnsAnd does the script need to create the new sheet?If so what name should we give to this new sheet.​

 

[My Aswer Is This]

Try this:
Run this script from sheet1

Sub My_Filter()
'Modified  7/25/2019  8:41:47 AM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim c As Long
Dim s As Variant
c = 2 ' Column Number Modify this to your need
s = "" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(1)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[diab0lik]

great does the job .thanks for your help

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

great does the job .thanks for your help

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub UnfilledTrackingNumbers()
  With Sheets("Sheet1")
    On Error Resume Next
    Intersect(.Range("B2:B" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlBlanks).EntireRow, .Columns("A")).Copy Sheets("Sheet2").Range("A1")
    If Err.Number Then MsgBox "There are no unfilled tracking numbers.", vbExclamation
    On Error GoTo 0
  End With
End Sub
[/td]
[/tr]
[/table]