Copy data based on on specific header "name"

[Efjoker]

Hi,
I’m using Excel 2007 and I have 2 files, source.xlsx and destination.xls. I have attached the sample files here.

I’m only interested in only a couple columns of data in “Working” worksheet in source.xls file. They are under the header “Part Number”, “In”, and “Category”. There are other columns in between these 3. However the number of column in this source file is not always the same. For example, the file this week may have only 2 random columns in between “Part Number” and “In”. Next week, the number of random columns in between them maybe 10. So it’s important for me to identify the data by the exact column header name.

I want to copy all data under header “Part Number”, “In”, and “Category” in source.xlsx, and then paste them to destination.xls (to be saved as Excel 97-2003 workbook). In “destination.xls”, the data should go to the row (insert new row) under the header “*Item Name”, “Qty Count”, and “Cat”. Also, under the header “New Name”, it will take the value from “*Item Name” then append a string “-NEW” to it.

Does anyone here know how to do this with vba code?

[natefarm]

Copy this to a code module in source.xlsx. You'll need to save it as a .xlsm.

Please Login or Register  to view this content.

[Efjoker]

The code works perfectly!!! Appreciate your effort and time. Many thanks

[Efjoker]

Since the source.xlsx is provided by another person, I don't want to repaste your code everytime he sends a new source file. I created a new A.xlsm and copied your code there. When I run the code from A.xlsm I want to be able to just select where the source.xlsx and destination.xls files are. I added these lines on top of your code.

Please Login or Register  to view this content.

But I'm stuck at this line:

Please Login or Register  to view this content.

I try to change it, but it doesn't work.

Please Login or Register  to view this content.

Can you help?

[Efjoker]

I managed to fix the error by adding this line before it.

Please Login or Register  to view this content.

[natefarm]

I would need to see the entire subroutine to evaluate.

[Efjoker]

Here is the code.

Please Login or Register  to view this content.

I find that if the sequence of heading in the source.xlsx changes, e.g. “Category” comes before “In”, the code will fail. Is it possible to use something like .Cells.Find("In", LookAt:=xlWhole) to solve this problem?

2 other things I’m trying to achieve here. I remove formatting before pasting the data to destination.xls, and try to process the category data by adding a string “_PASS_OK” to it in the same cell. The remove formatting part works but not for adding a suffix to category. I get Type mismatch error.

Also, I try to clear the selection in both source and destination file. I tried a few examples I found on the web, but this too doesn’t work.

[natefarm]

What you did with shtSource.Activate is a good solution. Here's the other way, just so the problem is clarified, but it's messier:

Please Login or Register  to view this content.

An even better way might be to reverse the order in which you open the workbooks. By opening shtSource last, it would already be the active workbook. That's up to you though.

Regarding the column sequence, that is resolved by resetting the value of SrcCol back to 3 before starting the search.

You can't concatenate a value (_PASS_OK) to a range of values with a single command. You have to loop through the range and add it to each cell.

By "clear the selection", I assume you to mean that you want to end up with a single cell selected, rather than a range, in both sheets. You can only select a cell and/or range on a sheet that is the active sheet. In your last line, above, you're trying to select a cell in shtTarget, but shtSource is the active sheet.

The code below will hopefully resolve all issues mentioned. I also updated the file open procedures to allow for cancelling the open.

Please Login or Register  to view this content.

[Efjoker]

Wow! You solved all the issues. Thank you!
I made one change to your code to fix the "_PASS_OK" part.

Please Login or Register  to view this content.

[Efjoker]

Just something extras to make the code even better.

Sometimes, the source file comes with an additional column "Class". I want to add a check for this heading. If found, the whole range from source file under Class heading is copied to column 5 in target file. If the heading is not found, it'll skip the copy. I use Goto command.

Please Login or Register  to view this content.

In shtTarget the column 2 "New Name" shows "100A21-NEW" but it actually stores a formula e.g =A12&"-NEW". To change them to value instead of formula. I added this code below the .formula line to reselect the range, copy, then paste as value only.

Please Login or Register  to view this content.

Lastly I want to make sure the column 3 "Qty Count" is rounded and has no decimal point. If value is 20.1, save it as 20. If value is 20.9, save it as 21. Again, I use the sample you make for adding "_PASS_OK" .

Please Login or Register  to view this content.

Is there a better way to make the code more efficient?

[natefarm]

Looks like you're handling it pretty well.

[Efjoker]

I have a very good start, all thanks to you.