hunsnowboard
02-08-2009, 05:35 AM
Hi Everyone! I try to make a macro which should be relatively easy but I am stucked with it for hours, and it is really getting on my nerves.
The real file which I am working on is really big, with around 65000 rows, and a few worksheets. The number of worksheets will growing in the future, so I need the macro to check all the worksheets (that is why I use the: for each worksheet in workbook command).
The point of the whole macro is to count the total codenumbers (which are in column "A"). The problem is that the codenumbers are repeated, so what I tried to do is to copy the whole column "A" to a separate worksheet (that is "List2") and then delete the repeating/duplicated codenumbers. Then again, copy the column "A" from the next workbook and paste it on "List2" column "B" and delete the repeating/duplicated codenumbers and then paste the remaining ones to column "A" on List2 worksheet. Then if the macro finished with all the workbooks, count the rows of column "A" on "List2" and then I will find out how many different codenames are in the workbook.
You can see my macro on the file attached. There are more problems with my macro. First of all, I have to run the macro from Sheet1 in order to start to copy column "A", if I run it from "List2" worksheet then nothing happens.
Second (if I start the macro from Sheet1) it copies only the "Sheet1" codenumbers, but it copies them three times... like ws next command would not work. Please try to help me! Thank you in advance!
The real file which I am working on is really big, with around 65000 rows, and a few worksheets. The number of worksheets will growing in the future, so I need the macro to check all the worksheets (that is why I use the: for each worksheet in workbook command).
The point of the whole macro is to count the total codenumbers (which are in column "A"). The problem is that the codenumbers are repeated, so what I tried to do is to copy the whole column "A" to a separate worksheet (that is "List2") and then delete the repeating/duplicated codenumbers. Then again, copy the column "A" from the next workbook and paste it on "List2" column "B" and delete the repeating/duplicated codenumbers and then paste the remaining ones to column "A" on List2 worksheet. Then if the macro finished with all the workbooks, count the rows of column "A" on "List2" and then I will find out how many different codenames are in the workbook.
You can see my macro on the file attached. There are more problems with my macro. First of all, I have to run the macro from Sheet1 in order to start to copy column "A", if I run it from "List2" worksheet then nothing happens.
Second (if I start the macro from Sheet1) it copies only the "Sheet1" codenumbers, but it copies them three times... like ws next command would not work. Please try to help me! Thank you in advance!