Copy Values from one sheet to another, merge duplicate values together

[abhiD]

hi,

I am trying to copy values from E1:E100 range in sheet 11"Software" to sheet "BOQ" under a specific heading whose address is set to change everytime. I have managed to write a code, seeking help for the web and modify it accordingly, I have to ignore the blank cell and a text string"refrnce No" and merge the same value together. I have skipped the merging part for now. The code I have written is.

Please Login or Register  to view this content.

But whenever I am trying to run the code, it is giving and error "Type Mismatch".
Advise me on this.

[Firefly2012]

Hi

Might you have error values in this range? That would cause a type mismatch. You could simply change .Value to .Text to access the text property of the cell (ie what is displayed rather than the actual value) which would avoid this particular problem.

[abhiD]

@firefly,

Thanks for the help..!
Ya, actually the values in the column "E" are filled using vlookup, and that V look up is typed in some other cell. The sheet Software has a dropdown box, on a certain selection a named range is copied and pasted in the A6 cell, now that range is situated at some other area starting at CZ columns. I dont why it is happening. Vlookup is working perfectly fine in the named range area and not in the copied area.

Newes, any idea how to merge the duplicate values in the column and then paste them in the "BOQ" sheet.

[smuzoen]

Could you post a sample workbook with no sensitive data - the things that do not make sense are
1. newRow will always =23 and is inside the loop so BOQ worksheet will always have all values written to A23
2. You set the range in column E however you are looking at the values in column A with dataRange.Cells(rowIndex, 1)
I am confused with the code - if you could post a sample workbook with what you are trying to achieve it would be easier.
To cycle through the data range you could

Please Login or Register  to view this content.

Are you trying to look at the values in Column E?

[abhiD]

@firefly,

Nothing is happening man..!! None of the values are getting copied..! Neither text nor numerical..!

[smuzoen]

If you want to remove the duplicates and only copy unique values to BOQ then apply an Autofilter and copy the unique records - Are the unique values in Column A? or E? - change code to suit - this case copies to column G in Software

Please Login or Register  to view this content.

[Firefly2012]

Here's a simple routine for extracting uniques using a dictionary object. Amend the lines in the code indicated to whatever suits your purposes:

Please Login or Register  to view this content.

[abhiD]

Hope you find this useful, and in turn i find it useful..!!

Just briefly, in the sheets meter acrh. , network arch. , software, and services a person has to choose many different things which you will get to know once you open the workbook. finally all the data choosen in these sheets have to to complied in the BOQ sheet, now the sheets are still being designed, hence the tool itself is incomplete and since m new at macros and coding. Hence, I am doing it one thing at a time.

thanks.!

[abhiD]

@firefly,

Nothing is happening man..!! None of the values are getting copied..! Neither text nor numerical..!

[Firefly2012]

In sheet Software in E1:E100 you don't actually have any values other than one cell with "Refrence No." that I presume you don't want to copy and one other cell with "#N/A" (I would check the formula that results in this value as it looks to me like it isn't pointing at the correct cell).

Hence, i am not surprised that the code is copying nothing over - there's nothing there to copy!

[abhiD]

@firefly
hey thanks man..! It is going just the way i wanted. Hey one more thing..can it insert the same no of row as the no of values it pastes in the other sheet..?
in addition..the column F contains the required quantity for the values in columns E, now if there are duplicate values in E can it sum the corresponding values and put the total in some other sheet in some other column.?

[abhiD]

In sheet Software in E1:E100 you don't actually have any values other than one cell with "Refrence No." that I presume you don't want to copy and one other cell with "#N/A" (I would check the formula that results in this value as it looks to me like it isn't pointing at the correct cell).

Hence, i am not surprised that the code is copying nothing over - there's nothing there to copy!

ya i know, but as i told you..am new to coding and the sheet is still being done single handed-ly!! i have made some errors, if you would go to named range "ionee" you would find the parent formula there,which i was trying to copy to the visible range using combobox selection. any advise to make it better and streamlined would be highly appreciated.