Hi All;
I am using excel 2003, not an expert and neither a programmer, but can do some basic programming.
I have 2 excel files (1st one COT_DATA.xls is destination file with multiple sheets and all sheets need to be processed same way. 2nd file is "annualof.xls" source file with lots of columns & data)
On destination file, I have button connected to macro, on press button. It insert line, read text/contents of cell A1, go to source file, look in column A and search the text, if found only first occurence then it will select entire row of active cell then copy and paste it back to destination file. Loop through all sheets in destination file. Working fine. Problem with this approach is copied row has lots of extra columns we do not need. After copy and paste into source file, we need to delete manually all extra columns for all sheets and we do not to do that.
1. On match of active cell/row, I want to copy only selected cells e.g. cell C, F, L or so. When I run macro for this approach, it gives me "Runtime error 9, subscript out of range" and high light code row "Worksheets(i).Select". If I put curser, (i) shows me value = 2 and first sheet in destination file has been processed but loop stuck in second iteration. I do not know what is wrong with For loop. Help please.
2. If no. 1 above get fixed then I want to match 2 columns from destination file/sheet to source file in 2 columns using IF AND condition. e.g. from destination file read text/contents of cell A1 & A4 (which is date), then go to source file, search first occurence in column A, if found then in active cell row, cell C search for date (value of cell A4 from destionation file/sheet). if both conditions match/true then copy selected cells from this row and paste back to destination file/sheet.
if solution of no. 2 above is same as no 1 above and only need to add AND with IF condition then ignore no.2, I will fix it but if need to use different approach then please fix it.
3. On workbook/file COT_DATA.xls, I want to have button linked to macro. On press it should show me file select dialogue box, where I can choose source file path/file and then that file path/file should be used as source file for processing. This source file path should remain untill I change, regardless closing and reopening workbook COT_DATA. We do use this functionaly, sometime when using different person on different computers.
Both files are attached. Please let me know if I can explain in better way or any question.
Files are too big so deleted lots of data but will give you to work on.
File COT_DATE, deleted sheets but you can create other sheets with Name: NG and enter test in A1: 'OATS - CHICAGO BOARD OF TRADE then search.
Thanks
Ria
I am using excel 2003, not an expert and neither a programmer, but can do some basic programming.
I have 2 excel files (1st one COT_DATA.xls is destination file with multiple sheets and all sheets need to be processed same way. 2nd file is "annualof.xls" source file with lots of columns & data)
On destination file, I have button connected to macro, on press button. It insert line, read text/contents of cell A1, go to source file, look in column A and search the text, if found only first occurence then it will select entire row of active cell then copy and paste it back to destination file. Loop through all sheets in destination file. Working fine. Problem with this approach is copied row has lots of extra columns we do not need. After copy and paste into source file, we need to delete manually all extra columns for all sheets and we do not to do that.
1. On match of active cell/row, I want to copy only selected cells e.g. cell C, F, L or so. When I run macro for this approach, it gives me "Runtime error 9, subscript out of range" and high light code row "Worksheets(i).Select". If I put curser, (i) shows me value = 2 and first sheet in destination file has been processed but loop stuck in second iteration. I do not know what is wrong with For loop. Help please.
2. If no. 1 above get fixed then I want to match 2 columns from destination file/sheet to source file in 2 columns using IF AND condition. e.g. from destination file read text/contents of cell A1 & A4 (which is date), then go to source file, search first occurence in column A, if found then in active cell row, cell C search for date (value of cell A4 from destionation file/sheet). if both conditions match/true then copy selected cells from this row and paste back to destination file/sheet.
if solution of no. 2 above is same as no 1 above and only need to add AND with IF condition then ignore no.2, I will fix it but if need to use different approach then please fix it.
3. On workbook/file COT_DATA.xls, I want to have button linked to macro. On press it should show me file select dialogue box, where I can choose source file path/file and then that file path/file should be used as source file for processing. This source file path should remain untill I change, regardless closing and reopening workbook COT_DATA. We do use this functionaly, sometime when using different person on different computers.
Both files are attached. Please let me know if I can explain in better way or any question.
Files are too big so deleted lots of data but will give you to work on.
File COT_DATE, deleted sheets but you can create other sheets with Name: NG and enter test in A1: 'OATS - CHICAGO BOARD OF TRADE then search.
Thanks
Ria