How to fill an Array with values from Range of cells, skip cells then add more values.

I have 2 workbooks, Array Source and Array Destination, right now the button (Create Purchase Request) in Array source will look at a the values in column R, and fill an Array (C to K) based on that value. Then insert that Array of Data into the Array Destination workbook. I would like to take the current Array of values but then skip columns and include column S in the Array. The Array destination workbook will get populated in columns C to K but then the added value from the S column will be in the X column. The code I that I am currently using is listed below.

Please Login or Register  to view this content.

Can you explain what are you trying to do?
You want to copy some data from one sheet to another.
Which part of data? What’s the criteria?
Here’s an example on how to populate 2 range into 2 array.

Please Login or Register  to view this content.

Excel 2007 32 bit

	A	B	C	D
1	name	job		unit
2	john	a		4
3	mark	b		5
4	rick	a		6
5	jane	b		7

Sheet: Sheet1

Excel 2007 32 bit

	A	B	C	D	E	F
1	name	job				unit
2	john	a				4
3	rick	a				6

Sheet: Sheet2

Akuini, sorry I wasn't very clear. Array source has multiple rows of data (material to order), the user sets the Status (specifies if the data is "Ready to Order") in column R. The code looks for a value of Ready to Order and then takes the values from that Row Columns C to K and continues down the list of Ready to Order Data. They then press the "Create Purchase Request" button, once it transfers the data to the Array destination sheet, it changes the Status to "Requested" and enters a number into the Purchase Request Number field (column T). Thus allowing the user to track what they have requested and identify what request number it was requested on.

The user also has the ability to set the status to "Return" if they want to send something back that is not actually needed. So if the user sets the status to "Return" the Purchase Request Number gets moved to column S (Line Item Comments - "Previously ordered on Purchase Request 2") and then the Purchase Request Number field for that row gets cleared. So when they press the "Create Purchase Request" button, the code also looks for a value of "Return" in column R and nothing in column T. If it finds the matching criteria, it transfers the item that is to be returned to the Array Destination sheet, then puts the new Purchase Request Number in the T column so that the next time they push the button it will not transfer that return request again. What I would like to be able to do is get the Line Item Comments transferred as well so that it is easier for the person who receives the Array Destination file to know that there is a line item that has to be Returned and not reordered, maybe even if the status says return, then then include that value in the array as well and transfer it to the correct column in the destination file.

Your sample workbook has no data. Can you fill in some data so I can test the code.

It’s a bit hard to follow, but I think I got the point.
Among other things, basically you need to update information in comments field & Line Item Status (in Array Destination) if there is changed in Line Item Status (in Array Source) from "Requested" to “Return”. Am I correct?
Note: Actually I can’t open ‘Array Destination.xlsm’ with ‘Workbooks.Open(fpath)’. I did adjust the path, still didn’t work, maybe because there are hyperlink that can’t be opened. So I open the file

Please Login or Register  to view this content.

Ok, glad you figured it out.
Sorry, I’m not good at explaining things, partly because English isn’t my native language. So you may read this link about With ..End With.
https://docs.microsoft.com/en-us/dot...with-statement