Alternative to Vlookup using macros

[Kemidan2014]

I am hoping this wonderful community can help me once again! with one problem of using Copy paste to paste on criteria from one work sheet another has been solved. i found that becuase my SOURCE worksheet for this task is 1000+ rows and 22 columns of Vlookups referencing a master file who will be overwritten as needed based on information we get from customers. This is causing a very long processing time for the newly added copy paste macro to work. i have found that if my Sheet 1 source data is just text it works INSTANTLY.

my solution needs to be my source sheet also needs to be a copy pasted data from our Masterfile instead of Vlookups. and as it wont be as easy as using the same macro.

I want to copy and paste all rows of data from my master file but i want to EXCLUDE certain columns because this information is irrelevent.

Source: Master file has headers in Row 1, 39 Columns and then Data below that about 1000+ rows of it and grows over time
Destination: Current working file "Sheet1" Same headers in Row 1 but only 23 of the columns of information that we want in this sheet

how would i target in VBA to only copy those specified columns and give me all the corresponding information below that column.

heres headers from source file

ToyotaData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	QIMS#	Doc Type	Instance Handle Key	Rank	Supplier Code	TMMC Supplier Code	Supplier Name	Part Name	Part Number	Overall Status	NCD Description	QEName	Full Name	Full Name	Model	SQA Rank	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	Occurence	Where NC found?	Will Parts Be Quarantined ?	Why Made Category	Why Shipped Category	NAMC	ST/CM Status	LT/CM Status	Standard	Actual	Assigned To
Data

heres what i want to get to

Customer database test 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	QIMS#	Doc Type	Rank	Supplier Code	Part Name	Part Number	Overall Status	NCD Description	Model	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	NAMC
Sheet1

I apologize but due to nature of the info i cant publically share it. just assume there 1000+ rows of data in each cell below up and that will grow over time so lastrow will always be different.

 

[DanteAmor]

I want to copy and paste all rows of data from my master file but i want to EXCLUDE certain columns because this information is irrelevent.

You only need to pass the data from book1 from sheet "data" to book 2 on "sheet1"

The sheet1 is empty or should the data go to the next available row?

 

[DanteAmor]

In column A "QIMS#" of sheet1, do you have data and based on that data you want to get the information from the master book?

 

[Kemidan2014]

In column A "QIMS#" of sheet1, do you have data and based on that data you want to get the information from the master book?

To answer your first question let me give you some more back ground on the master file that we download from our customers website in order to get the latest complaints.
The data under the headers has no order. so the new information could suddenly appear in the middle of spreadsheet.
So to combat that i would like to simply replace the old information book 2 Sheet1 with Book 1 sheet1 but with the limited columns UNLESS there's truely is a magical way it can still acquire the new info since Dates are involved please share!

Column A QIMS# would not have data until it is copied from Master file (book1) Sheet Data

I actually worked out a macro that will copy the whole sheet but it opens the book, copies, but then stops because i have to answer question about clip board memory. pastes and overwrites all information then it closes the master book and saves the file i pasted in. its the stopping to have to answer a question i dont like i was hoping to do stuff in the background with simple macro that i could assign to a button.

 

[DanteAmor]

I'm lost. Help me with real names of books and sheets. Continuing with your example, you want to copy the data from the book "ToyotaData.xlsx", sheet "Data" and paste in the book "Customer database test 2.xlsm", sheet "Sheet1".

I don't understand why you mention the Vlookup function, what are you looking for?
Now you mention the dates...

It would help if you put an example with generic data on each sheet, before and after. Just put 2 records with general data for me to understand.

I have more doubts, but if you give an example with the before and after, I think with that I would understand.

 

[Kemidan2014]

Here is an example of the Before Data in its which is my Source work book

ToyotaData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	QIMS#	Doc Type	Instance Handle Key	Rank	Supplier Code	TMMC Supplier Code	Supplier Name	Part Name	Part Number	Overall Status	NCD Description	QEName	Full Name	Full Name	Model	SQA Rank	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	Occurence	Where NC found?	Will Parts Be Quarantined ?	Why Made Category	Why Shipped Category	NAMC	ST/CM Status	LT/CM Status	Standard	Actual	Assigned To
2	01-01016-V6-5000	QPR	Irrelevent Data 1	B	0101-6		Aisin Automotive Casting - Tennessee	Part 1	11111-11111	Officially Released, Awaiting LTCM Plan	FIT CONDITION	QE	AM	Mgr	V6					5/3/2021	4/28/2021								First Time	GR-KAI	false			Cust 1	STCM Received		GASKET HAS TO BE SEATED ALL THE WAY AROUND, CANNOT BE STICKING UP	HEAD COVER GASKET NOT SEATED	205519
3	01-01016-V6-5001	QPR	Irrelevent Data 2	B	0101-6		Aisin Automotive Casting - Tennessee	Part 4	22222-22222	LTCM Plan Accepted, Awaiting LTCM Actual	LOOSE	QE	AM	Mgr	V6					5/3/2021	4/28/2021								First Time	GR-KAI	false			Cust 2	STCM Received		GASKET MUST STAY IN PLACE	GASKET OUT OF GROOVE	205519
4	01-01016-V6-5002	QPR	Irrelevent Data 3	C	0101-6		Aisin Automotive Casting - Tennessee	Part 3	33333-33333	Closed-Cancelled	POPPING / PULLING OUT	QE	AM	Mgr	V6					5/3/2021	4/28/2021								First Time	GR-KAI	true			Cust 2	STCM Received		GASKET TO BE SEATED AND NOT "OUT" OF THE GASKET GROOVE	GASKET IS NOT SEATED WHEN CONVEYANCE IS LOADING THE PART	205519
5	01-01016-V6-5003	QPR	Irrelevent Data 4	B	0101-6		Aisin Automotive Casting - Tennessee	Part 2	44444-44444	Closed-Cancelled	WRONG PART	QE	AM	Mgr	V6					3/8/2022	3/7/2022								First Time	NONC	true			Cust 2	Awaiting STCM		Correct bolt with 8 marking	Wrong bolt with no 8 marking	205519
Data

Here is example of the data of the after which is in the Destination work book

Customer database test 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	QIMS#	Doc Type	Rank	Supplier Code	Part Name	Part Number	Overall Status	NCD Description	Model	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	NAMC
2	14-01016-TNGA-5000	QPR	B	0101-6	Part 1	11111-11111	Officially Released, Awaiting LTCM Plan	EXCESS MATERIAL	TNGA	4/12/2022				3/23/2022	3/28/2022							Cust 1
3	01-01016-V6-5001	QIR	B	0101-6	Part 4	22222-22222	LTCM Plan Accepted, Awaiting LTCM Actual	ADHESIVE NG	V6	4/7/2022	4/15/2022			3/22/2022	3/23/2022	3/31/2022	4/4/2022					Cust 2
4	01-01016-V6-5002	QPR	C	0101-6	Part 3	33333-33333	Closed-Cancelled	POPPING / PULLING OUT	V6				5/3/2021	4/28/2021								Cust 2
5	01-01016-V6-5003	QPR	B	0101-6	Part 2	44444-44444	Closed-Cancelled	WRONG PART	V6				3/8/2022	3/7/2022								Cust 2
Sheet1

 

[Kemidan2014]

So to reitterate on my current Destination work book "Customer Database Test - Sheet 1" were all the data from row 2 down to row 2600 is all Vlookups to Source workbook "Toyotadata - Data"
and id like to pull the same information i have on customerdatabase Test but not using Vlook ups because of processing time for updating

 

[DanteAmor]

Your example confuses me. In the destination workbook in the column "QIMS#" you have this data 14-01016-TNGA-5000, but that data is not in the source workbook ...

There are other data that are in the source, which do not appear in the destination.

So I still don't understand. If you have 4 records at the source, you should pass 4 records to the destination with all your data. (obviously the required columns, that part I already understood), but I do not understand why in the destination there are different data.

It is necessary that you put the destination sheet twice, once with the data before any execution, and the second with the expected result.

That is, I need to see 3 sheets, the source sheet, the destination sheet before execution and the same destination sheet with the expected results.

 

[Kemidan2014]

Ok i apologize on confusing you, i alterred some of the data for privacy reasons but forgot to copy some of the dates over so i will repost but ensure all cells are test data is correct for you.

here is SOURCE sheet

ToyotaData2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	QIMS#	Doc Type	Instance Handle Key	Rank	Supplier Code	TMMC Supplier Code	Supplier Name	Part Name	Part Number	Overall Status	NCD Description	QEName	Full Name	Full Name	Model	SQA Rank	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	Occurence	Where NC found?	Will Parts Be Quarantined ?	Why Made Category	Why Shipped Category	NAMC	ST/CM Status	LT/CM Status	Standard	Actual	Assigned To
2	01-01016-V6-5000	QPR	Irrelevent Data	B	0101-6		Aisin Automotive Casting - Tennessee	Part 1	11111-11111	Closed-Cancelled	FIT CONDITION	QE	AM	MGR	V6					5/3/2021	4/28/2021								First Time	GR-KAI	false			Cust 1	STCM Received		GASKET HAS TO BE SEATED ALL THE WAY AROUND, CANNOT BE STICKING UP	HEAD COVER GASKET NOT SEATED	205519
3	01-01016-V6-5002	QPR	Irrelevent Data	B	0101-6		Aisin Automotive Casting - Tennessee	Part 2	22222-22222	Closed-Cancelled	LOOSE	QE	AM	MGR	V6					5/3/2021	4/28/2021								First Time	GR-KAI	false			Cust 1	STCM Received		GASKET MUST STAY IN PLACE	GASKET OUT OF GROOVE	205519
4	01-01016-V6-5001	QPR	Irrelevent Data	C	0101-6		Aisin Automotive Casting - Tennessee	Part 1	11111-11111	Closed-Cancelled	POPPING / PULLING OUT	QE	AM	MGR	V6					5/3/2021	4/28/2021								First Time	GR-KAI	true			Cust 1	STCM Received		GASKET TO BE SEATED AND NOT "OUT" OF THE GASKET GROOVE	GASKET IS NOT SEATED WHEN CONVEYANCE IS LOADING THE PART	205519
5	01-01016-V6-5003	QPR	Irrelevent Data	B	0101-6		Aisin Automotive Casting - Tennessee	Part 3	33333-33333	Closed-Cancelled	WRONG PART	QE	AM	MGR	V6					3/8/2022	3/7/2022								First Time	NONC	true			Cust 1	Awaiting STCM		Correct bolt with 8 marking	Wrong bolt with no 8 marking	205519
6	01-01016-V6-5004	QIR	Irrelevent Data	C	0101-6		Aisin Automotive Casting - Tennessee	Part 4	44444-44444	Initial Release - Awaiting STCM	DIMENSION	QE	AM	MGR	080Y,417W,434W,502W,511W,512W,537W,541W,567W,574W,						4/6/2022								Recurrence	AR Upper Line	true			Cust 2			Parts must meet all specs.	Part had scratch on seal surface.
Data

Here is Destination sheet Before Execution

Customer database test 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	QIMS#	Doc Type	Rank	Supplier Code	Part Name	Part Number	Overall Status	NCD Description	Model	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	NAMC
2	01-01016-V6-5000	QPR	B	0101-6	Part 1	11111-11111	Closed-Cancelled	FIT CONDITION	V6				5/3/2021	4/28/2021								Cust 1
3	01-01016-V6-5002	QPR	B	0101-6	Part 2	22222-22222	Closed-Cancelled	LOOSE	V6				5/3/2021	4/28/2021								Cust 1
4	01-01016-V6-5001	QPR	C	0101-6	Part 1	11111-11111	Closed-Cancelled	POPPING / PULLING OUT	V6				5/3/2021	4/28/2021								Cust 1
5	01-01016-V6-5003	QPR	B	0101-6	Part 3	33333-33333	Closed-Cancelled	WRONG PART	V6				3/8/2022	3/7/2022								Cust 1
Sheet1

Here is Destination sheet AFTER execution

Customer database test 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	QIMS#	Doc Type	Rank	Supplier Code	Part Name	Part Number	Overall Status	NCD Description	Model	LT CM Plan Due	Original LTCM Actual Due Date	Revised LTCM Actual Due Date	Close	Initial Issuance Date	Official Issuance Date	LTCM Plan Submitted	LTCM Plan Accepted Date	LTCM Plan Rejection Date	LTCM Actual Submitted	LTCM Actual Accepted Date	LTCMActualRejectionDate	NAMC
2	01-01016-V6-5000	QPR	B	0101-6	Part 1	11111-11111	Closed-Cancelled	FIT CONDITION	V6				5/3/2021	4/28/2021								Cust 1
3	01-01016-V6-5002	QPR	B	0101-6	Part 2	22222-22222	Closed-Cancelled	LOOSE	V6				5/3/2021	4/28/2021								Cust 1
4	01-01016-V6-5001	QPR	C	0101-6	Part 1	11111-11111	Closed-Cancelled	POPPING / PULLING OUT	V6				5/3/2021	4/28/2021								Cust 1
5	01-01016-V6-5003	QPR	B	0101-6	Part 3	33333-33333	Closed-Cancelled	WRONG PART	V6				3/8/2022	3/7/2022								Cust 1
6	01-01016-V6-5004	QIR	C	0101-6	Part 4	44444-44444	Initial Release - Awaiting STCM	DIMENSION	I4					4/6/2022								Cust 2
Sheet1

 

[Kemidan2014]

Hope this makes it easier! =)