Find, Cut and Paste Macro

[Realrookie]

Hello all,

A little experience with VBA.
I did search the forum and found several similar posts but I cannot modify any opf them due to lacking knowledge.

I have a master sheet with over 30k lines of data. From here in Sheet1, Column B i have to find the customer reference, cut that row (or rows if multiple available) and paste it in the Sheet2 on the next empty cell in Column A.

I have seen several macro's on this forum that all use pre-defined search terms but none that has a find "Box".

Any help or pointers would be greatly apreciated.

Red

 

[Realrookie]

I figured it out:

Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)

Thank you very much for your code

 

[Realrookie]

Scrap that. I changed copy to cut and it now fails.

Sub CopyPaste2()
Dim lr&
Worksheets("Sheet1").Activate
lr = Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.AutoFilterMode = False
Range("A1:H" & lr).AutoFilter field:=2, Criteria1:=InputBox("Customer reference: ")
Range("A2:H" & lr).SpecialCells(xlCellTypeVisible).Cut
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
'Sheets.Add after:=Worksheets("Sheet1")
'ActiveSheet.PasteSpecial (xlPasteValues)
Worksheets("Sheet1").AutoFilterMode = False
Application.CutCopyMode = True
End Sub

 

[Realrookie]

Option Explicit
Sub CopyPaste()
Dim lr&
Worksheets("Sheet1").Activate
lr = Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.AutoFilterMode = False
Range("A1:H" & lr).AutoFilter field:=2, Criteria1:=InputBox("Customer reference: ")
Range("A1:H" & lr).SpecialCells(xlCellTypeVisible).Copy
Sheets.Add after:=Worksheets("Sheet1")
ActiveSheet.Cells(1, 1).PasteSpecial (xlPasteValues)
Worksheets("Sheet1").AutoFilterMode = False
Application.CutCopyMode = False
End Sub

I changed the code to the following (and it works as long as data is visible):

Sub CopyPaste()
Dim lr&
Worksheets("Sheet1").Activate
lr = Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.AutoFilterMode = False
Range("A1:H" & lr).AutoFilter field:=2, Criteria1:=InputBox("Customer reference: ")
Range("B2:H" & lr).SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
Worksheets("Sheet1").Activate
    With ActiveSheet.AutoFilter.Range
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    End With
Worksheets("Sheet1").AutoFilterMode = False
Application.CutCopyMode = False
End Sub

My final question: Is it possible to have the macro stop when no data is available? When I tried to run the code for a 2nd time with the same filter as the 1st, it errors out. Basically when there is no data available, it stops.