Thanks Kruncher, that did the trick. I figured you would be the one to respond if any.
We have 27 piezometers installed out at one of our lakes (tube in the ground that measures water pressure). A Datalogger reads the stored data and saves it into text files.
AS a result, I have 27 text files with 6 months of data each. The data is date, pressure, and temperature obtained every hour for the past 6 months (3 columns, lots of rows).
I have 27 *.xlsx files, that I created that processes the text file data with some formulas , plots it, etc. Currently there is about 2 years of data for each piezometer, and we add to it every 6 months or so. I also have three other excel files with hourly data (lake level, tailwater level, and barometric pressure) that get fed into the "processing and plotting file" for each piezometer.
Previous method used by others for updating the "processing" files involved looking at the processing file, seeing the last date and hour, then manually copying over the temperature and pressure from the next corresponding hour up to the most current date and time available. Same thing for lake level, tailwater, and barometric pressure. WASTE of time!
I'm developing a macro that we can run every 6 months when we download the new data to speed things up.
Currently I have my code set to open the processing xlsx file, find the last row# in the date column, then save the row number and date value in that cell. Next it opens the piezometer data Text file, splits the text file into appropriate cells based on a fixed width, does a "Match" command on column A to find the row number that corresponds to the last date found in the "processing" xls file.
Next, I needed to select and copy all three columns of data starting from the next hourly row in the piezometer data text file and paste it into the first blank row in the processing file, which is where your formula does the trick.
I'll have to do the same thing for the barometer, lake level, and tailwater spreadsheets. Next step is to copy the plotting formulas into all of the new rows. Then I will save the work book close it out.
I hope to repeat all of that inside of a loop of some sort that counts from 1 to 27 opening the files for PZ-1 through PZ 27.
My plan is to store the macro in a separate workbook that will open all of the other workbooks as needed during the data copy and paste, etc.
