| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 83953 | 2007-10-19 05:18:00 | Excel Macros | nana (1229) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 603000 | 2007-10-19 05:18:00 | good day... i need help on this macro thingy! i'm importing data from a .txt file that will be in sheet 1. This set of data will always be changing. from there, i need to copy dates and figures to sheet 2 with formulas. 1) for a date in sheet 1, say cell C3, how do i create a macro that will paste to sheet 2 cell A6 and go to the next empty cell? 2) and from the next empty cell, repeat point 1 and go to the next empty cell and so on... |
nana (1229) | ||
| 603001 | 2007-10-20 01:18:00 | Hi and welcome to Press F1! We may need a little more information. The txt file - are the columns always in the same layout / format? If so, what is the layout? Also, when you import the data, do you always start with a clean sheet 1? Or do you want the new data to be imported below the existing data in sheet 1? Lastly, which pieces of data (i.e. columns) do you want copied to sheet 2? Also, which rows in particular - is there anything special or particular about the rows you want to copy? Or simply all of them? Andrew |
andrew93 (249) | ||
| 603002 | 2007-10-20 07:16:00 | hi andrew... thks for replying... here's it.. after i import the txt file, the format will be as such SHEET 1: B3 : Country C3 : Date B4 - D7: numbers Sheet 1 is always changing, which contains raw data. that is, i have to always import new data. so it'll overwrite the old data. or should i import to a clean sheet?? i'm not too sure as well. but when i import a new set of data, it should have the same format so that i can run the macro automatically right? Sheet 2 will be the report itself, with some formulas at the end after filling in the raw data. that is y when i record the macro, i need it to be: Date from sheet 1, c3 should go to sheet 2, a6 and go to the next cell a7 country from sheet 1, b3 should go to sheet 2, d6 and go to the next cell d7 and so on... But how do i record it so that it will keep going down to the next empty cell?? thks alot! |
nana (1229) | ||
| 603003 | 2007-10-21 01:08:00 | Hi Yes it is a good idea to keep importing to Sheet 1 and clearing it each time you want to run this. And yes having a consistent format/layout is very helpful. Forget about recording a macro at the moment - the reason I say this is because we can write some VBA code which will take care of finding the next available row (although if you want to experiment then record a macro and navigate your way around Sheet 2 using just the keyboard arrows and End and Home keys etc to see how it codes it). I also think we will need some kind of loop which you won't be able to create using the recorder (but we can create it using VBA). But I do need to know a couple more things. So if I am understanding your question correctly, the value in cell C3 from Sheet 1 should be copied to the next available row in column A and the value in cell B3 from Sheet 1 should be copied to the same row in column D - I also suspect there will be quite a bit more to copy? Which values in the range B4 : D7 do you want copied? And where to? All on the same row or spread over a number of rows? And is there anything else? Andrew |
andrew93 (249) | ||
| 603004 | 2007-10-21 13:13:00 | hi andrew... very grateful in yr interest in helping me... :cool: hmmm.. VBA is visual basis a?? pardon me but i'm not good with programming at all!! :( is there a way to send an attachment in here? it'll b better if i can send u an example.. nana |
nana (1229) | ||
| 603005 | 2007-10-21 13:17:00 | actually i've managed to do it using an IF function for each cell in sheet 2 but i have to record each macro for each data - that is, have one macro for date, another for country name etc.. i cant seem to record all at once.. the figures will just overwrite the first row each time i run the macro... nana |
nana (1229) | ||
| 603006 | 2007-10-21 19:25:00 | If the data on sheet 1 is always consistent (ie same number of cells used each time for same data) can you just use = ? eg - Go to sheet 2 - Select cell A1 - type = - Go to sheet 1, cell A1 - Press Enter Whenever the data in Sheet 1 A1 changes Sheet 2 A1 then updates Repeat for all required cells |
Mercury (1316) | ||
| 603007 | 2007-10-22 03:13:00 | The issue is a little more complex than that. Nana - you have a PM with my e-mail address. Andrew |
andrew93 (249) | ||
| 1 | |||||