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

 

[bebo021999]

With over 30k rows, its better that using VBA autofilter, then copy visible rows at one, and paste to other sheet
Without image/screenshot sample, its hard to see how the data look like. Try.

 

[Realrookie]

With over 30k rows, its better that using VBA autofilter, then copy visible rows at one, and paste to other sheet
Without image/screenshot sample, its hard to see how the data look like. Try.

I have added a screenshot of the sample file (which I could not upload here). I was thinking of autofilter but got stuck on the criteria, as this changes.

 

[bebo021999]

can you try XL2BB tools? XL2BB - Excel Range to BBCode

 

[Realrookie]

can you try XL2BB tools? XL2BB - Excel Range to BBCode

Sample.xlsx
	B
266
Sheet1

 

[Realrookie]

Sample.xlsx
	A	B	C	D	E	F	G	H
1	CreationDate	Customer reference	ISBN	Book Type	Title	Qty	Publisher	Unit Price
37	04/04/2022	7000067283	9780367501655	Soft	Glasgow (High-Rise Homes, Estates and Communities in the Post-War Period)	1
38	04/04/2022	7000067284	9780367864965	Soft	Introduction to BioMEMS	1
39	04/04/2022	7000067284	9781138579415	Soft	ASR Sketching as Design Thinking	1
40	04/04/2022	7000067284	9780415787307	Soft	Digital Media Foundations (An Introduction for Artists and Designers)	1
41	04/04/2022	7000067285	9781138083875	Soft	Archipelago Tourism (Policies and Practices)	1
42	04/04/2022	7000067285	9780415748537	Soft	(Infrastructural Lives)	1
43	04/04/2022	7000067285	9781138924512	Soft	An Introduction to Economic Geography (Globalisation, Uneven Development and Place)	1
44	04/04/2022	7000067286	9781611322996	Soft	(Paleoethnobotany, Third Edition (A Handbook of Procedures))	1
45	04/04/2022	7000067287	9781138490468	Soft	Ideological Equals (Women Architects in Socialist Europe 1945-1989)	1
46	04/04/2022	7000067288	9780367133177	Soft	Ear, Nose and Throat Diseases of the Dog and Cat	1
47	04/04/2022	7000067288	9781498754415	SQB	Essentials of Clinical Anatomy of the Equine Locomotor System	1
49	04/04/2022	7000067290	9780367449834	Soft	Flexural-Torsional Buckling of Structures	1
50	04/04/2022	7000067290	9789054101864	SQB	Support of Underground Excavations in Hard Rock	1
51	04/04/2022	7000067290	9780419160304	Soft	Underground Excavations in Rock	1
52	04/04/2022	7000067291	9780415567671	Soft	Religious Studies	1
53	04/04/2022	7000067292	9780415672573	Soft	Reading Images (The Grammar of Visual Design)	4
54	04/04/2022	7000067292	9781138048744	Soft	Academic Writing (A Handbook for International Students)	4
256
Sheet1

 

[bebo021999]

which customer criteria to filter?
and criteria come from a cell, or from input box?

 

[Realrookie]

which customer criteria to filter?
and criteria come from a cell, or from input box?

If it can be done from an input box, that would be great, as currently, I am using Find (to find the customer reference) then cutting the entire row (or rows) and paste in Sheet2.

 

[bebo021999]

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

 

[Realrookie]

Sheets.Add after:=Worksheets("Sheet1")

You are a godsend. But as Sheet2 is already active (at all times as that becomes the manifest), I want add the copied data into the next empty Row with

Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

But then I get a 400 error. Headers on Sheet 2 are always active as well.