Copy data from multiple sheet based on a criteria
I have multiple workbooks and also 1973-2008 worksheets in each workbook. I have some station name in each worksheets. Now I am in need of time series(date and time in one coloumn and value in other coloumn) for each station. I can simply copy and paste but it is very difficult. How I can do it!. Can anyone help me!
I am attaching one sample workbook in here.
Hi,
I had a quick look at your attachment.
What I am unsure of is exactly what you expect the output to be. Can you update your spreadsheet to clearly show what you are expecting as the results of any macro (or whatever) to produce.
jmac
I am updating the output file.
here you go! I hope this is what you need.
Let me know if it works & if you need explanations.
-Phil
Please Login or Register to view this content.
! Thanks a lot! My problem has been solved.
Can you please explain me the macro? I can learn it for further use.
Hi nishan.biswas
If the suggestion helps you, then Click * to Add Reputation & give your comments
I think I left a lof of .select so that you can follow up if you go through it by clicking F8.
I would recommend for you to paste the code in a module and ensure that you have the sheets 1973 and 1974 in the excel document.
1) I define the range per year and station. You ll notice that your station data appear every 50 rows. So using a loop I define What I call the rangeofinterest in the code.
2) within this range I look for the name of the station which is 2 column away from the title and save it in a variable
3) within this same range I look for "DATE". As you know Jan is 1 cell away from the cell Date, Feb is 2 cell away etc.
4) I start for the column Jan which is conviently 1 row away from Date and go down in the days until 31, saving each value in an array
5) then I go to february and loop through the days of that month... I do this until 12 columns away from Date (December)
6) exit the loop and start with the next station 50 rows below
7) repeat the same process for each station
8) drop all the values saved in the array in the sheet called "output"
Again I think it would become clear if you F8 through the code as you can follow what each line does.
I hope it helped you.
let me know if you have specific questions about the code.
- Phil
Last edited by mortphil; 01-21-2014 at 10:52 AM.
Dear Phil,
I have got two errors when I wanted to execute the code to excel macro.
1. Runtime error 91: Object variable is not set
2. Runtime error 9: Subscript out of range.
Can you please help me to find out the solution???
Best regards
Nishan
Dear Phil,
Please correct me the following error:-
" Runtime error 9: Subscript out of range. "
Can you please help me to find out the solution???
Best regards
Nishan
Hi -
Give this a shot as well;
event
post the excel file with the code I ll try to debug through it to see what s wrong.
This being said EVENT21 solution is much better than mine.
Phil
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks