automatically pull data from one sheet to another one

[lauro]

hello everybody,

my workbook is setup in such way that everyday it will create a new worksheet and name it with the current date. What I need to do now is to get some data (always in the same range) from the newly created workbook and paste it into a different worksheet. The copied data should go to the next available (empty) cells.

Any idea on how to achieve it?

[Mumps1]

Ciao Lauro. I'm a little confused. Is it a newly created worksheet or a newly created workbook? What is the range you want to copy. What are the source and destination sheet names? It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could attach a copy of your file. Include a detailed explanation of what you want to do referring to specific cells and sheets.

[lauro]

Hello Mumps, thanks for your reply. Attached please find the workbook I am working on. When I attached it to this reply it contained only one worsheet called Report. When you open it, it will create a new sheet and name it with today's date.

These sheets contain data concerning some flights operated by a certain airline. The data I need to copy from one sheet to a "cumulative one" is in different cells:

H4
V4
I22:I30
AD22:AD28
I32
L32
O32
L33
R33
I39:Y44

the thing is that I don't know how to have the data from a new worksheet (as tomorrow my program will create a new worksheet with a different name). I also would like to prevent the code to create duplicates (H4 -date - and V4 could be the "check" cells)

I didn't create a "receiving" sheet yet so feel free to experiment as much as you want.

Thanks again for your help

[Mumps1]

When you open the file, you have a sheet called "Report" and a copy of that sheet with today's date as the name. You want to copy H4, V4, I22:I30, D22:AD28, I32, L32, O32, L33, R33, I39:Y44 from "Report" to the same location in the newly created sheet. Tomorrow another copy of "Report" is created with tomorrow's date. Is this correct? If it is correct, I don't' quite follow what happens next. Could you explain step-by-step what happens next? Do you want the same range of cells copied to tomorrow's sheet as well or will the contents of the range of cells change? If the data to be copied changes, how does the change take place? Also what do you mean by "cumulative one"? It implies that you want all data on one sheet.

[lauro]

What I would like to achieve to to have a worksheet where all the data is inserted in rows. As you can see we are talking about passengers and cargo and excess luggage. This new sheet (lets call it "Statistics") should receive its data from the various sheets that are created daily. As you have seen, now you have a sheet called report and another sheet called with today's date. Tomorrow a new sheet will be created and it will be called with tomorrow's date, and so on. As you can see "Report" is the sheet that will always be copied, it is the sheet that creates the new ones. So the ranges that I need to copy to "Statistics" are always in the same cells. Data in the new sheets is inserted via the userform. The data, of course, varies depending on how many passengers, bags, cargo, etc. that is carried.

attached please find another test sheet. as you can see I have created "Statistics". Right now the data inserted is taken from the sheet called with today's date. How can I take the same data from tomorrow's sheet? and from the day after tomorrow's? and so on... and making sure that it always goes to the next available free line....

[Mumps1]

Just a few questions for clarification.....
In the "Statistics" sheet:
-where does the value 1254 in cell J3 come from?
-it appears that the values in M3 and N3 are reversed.
-cells R3 and S3 have the same value ... is this correct?

In your original post, you say that you want to copy I39:Y44 from the newly created sheet to "Statistics". If this range has data in all cells (30 cells), where on the "Statistics" sheet do you want to copy all 30 cells? It looks like you want the data from each newly created sheet to be copied to one row. Also, in I39:Y44 you have a "PNR" but I don't see a column in "Statistics" for this value. Do you want the "PNR" to be copied over and if so, where?

[lauro]

Ciao Mumps,

1. value 1254 in J3 is a mistake. what I need to do is to split the values in cell I3 so that the pieces (158) are in I3 and the weight in kgs (2500) in J3. the same is valid for cells R3 and S3
2. yes, M3 and N3 are reversed. This is also a mistake.

Correct: in my original post I stated that I wanted to copy some ranges. I have taken those ranges and split them into cells. All the data needed is now in "Statistics"

PNR is the reservation number and I don't need it for statistics.

[Mumps1]

It looks like the best place to put this code is in the code module for your "REPORT" form. This form appears to be the last form that is completed which means that the newly created sheet will have all the necessary data inserted. Copy and paste the code below to insert it just above this line

Please Login or Register  to view this content.

in the code module for your "REPORT" form. Please note that the copying of the data to the "Statistics" sheet will be done when you click the "Continue" button in the "REPORT" form. Please let me know how it works out.

Please Login or Register  to view this content.

[lauro]

Mumps, it is exactly what I was looking for (I have tried with 2 different dates...) there are only 2 things I need to ask you:

1. when the data is transferred to "Statistics" in the the active worksheet there is a range (ad22:ad26) that remains selected
2. "Sheets("Statistics").Cells(Rows.Count, "R").End(xlUp).Offset(1, 0) = Split(Sheets(Format(Date, "dd-mm-yy")).Range("AD27"), "/")(0)" is there a reason why this is formatted as date? it does the job nicely and I am trying to understand what you did.

thank you very much for your help

I forgot: there are 2 more cells that I would like to add in Statistics: in cells AD and AE I would like to add the data coming from cell X18. This should also be split in 2, the part following the "/" should be formatted as time hh:mm (the being the delay code and the second one the amount of delay)

[Mumps1]

Add this line of code:

Please Login or Register  to view this content.

just above this line:

Please Login or Register  to view this content.

This will take care of the range that remains selected.

Please Login or Register  to view this content.

actually represents the name of the newly created sheet. When you use the 'Date' function in Excel, it returns the date in mm-dd-yy format. Since the name that is given to the sheet when the sheet is created is in "dd-mm-yy" format, the code has to refer to the sheet name in the same format in which it was created and that is what 'Format(Date, "dd-mm-yy")' does. I hope that helps.

[lauro]

perfect... can I add one last request?

[Mumps1]

No problem.
One question for you. In the ranges copied from the newly created sheet, will any of the cells ever be blank? If any of those cells is ever blank, the copied data in the corresponding column in the "Statistics" sheet will not be copied to the correct row. The macro assumes that there are no blank cells in the copied ranges. Please let me know. I may have to modify the macro to account for blank cells.

[lauro]

you are reading my mind... that is exactly what I was going to ask... yes, there are cells that will be blank if nothing happened for that particular item.

Also, I have noticed that if I click on continue the data will be inserted into statics even if the same exact data has already been inserted. Is there a way to prevent that from happening? the unique keys are the date and the flight number.

Last... if you look in any worksheet you can see that cell AJ5 contains the aircraft configuration. is it possible to have vba to sum the configuration (both C and Y) and then give me a % of what has been carried for both departing and arriving flights based on the total passengers (I22 and AD22 - two different percentages, one for the departing and one for the arriving flights. Aircraft configuration does not change as it is the maximum capacity)?

[Mumps1]

I think that you may want some flexibility to change or add data to the same dated sheet. This would allow you to enter only some of the data and then go back on the same day and enter additional data or change some of the data that was already entered. This, of course, would have to be done on the same day. This would also solve the problem of data duplication. Would that work for you?
I'm afraid that I don't follow what you want to do with your last request. Cell AJ5 contains "C8Y156" which you say is the aircraft configuration. When you say " sum the configuration (both C and Y)" do you mean add 8+156? Cell I22 contains 158 and cell AD22 contains 141. What would be the expected results in terms of percentages for this data (one for the departing and one for the arriving flights)? I want to get an idea of how you would calculate the percentages. Where would you want to put the percentages once they have been calculated? Could you also give me a few more examples of other aircraft configurations?

[lauro]

adding data on the same day and not having any duplicates would work fine with me. In fact flights are closed on the same day.

about the configuration: the A319 had 8 business class seats and 156 economy seats. 8 + 156 = 164. if a flight is full then the it is 100% full, if it carries 141 passengers it is 85,98% full. I would put the percentage in column D, right after the Total pax (for outgoing flights) and in column N, for the incoming flights

Aircraft configuration changes according to the type of airplane. the bigger the airplane the more seats. In the actual program I did create a sheet called Fleet with all aircraft types and configurations. Cell AJ5 has a vlookup formula that gets the data from "Fleet"

[Mumps1]

At the moment columns D and N have the number of adults. Do you want to insert new columns after columns D and M to contain the percentages or do you have something else in mind?

[lauro]

I was thinking that putting the percentages immediately after the total would make more sense. So: total, percentage, adult, child and so on....

[Mumps1]

OK. I will start to work on these latest changes. I still need to get an idea of what the various configuration codes may look like. For example, C8Y156 has two numbers in it, the 8 and the 156. Are there any configurations that may have only one number or more than two numbers, for example Y250 or X6C8Y156? This is important to know because I need to know how many numbers to sum. On another note, the "Statistics" sheet in the file you last posted has formulas in row 3. You should remove all the formulas in this sheet so that they don't interfere with the macro.

[lauro]

max configuration can be cxxyxxx for our aircraft.

Feel free to delete all the formulas. My problem is that I can use formulas but cannot put them in a VBA.

[Mumps1]

Place this code in the code module for your REPORT userform as you did before.

Please Login or Register  to view this content.

Place this function in a regular code module:

Please Login or Register  to view this content.

Before you use this code, insert the two new columns for the percentages in the "Statistics" sheet. You don't need the formulas in this sheet because the macro inserts that data for you so you can remove them.

[lauro]

Mumps, attached please find the file with the new code. I have tried it and the only info I get inserted is the date and the percentages.

[Mumps1]

Ciao lauro. The problem was being caused by the fact that cells A1 and B1 on the "Statistics" sheet were merged cells. You should avoid using merged cells if at all possible because they almost always create problems if you are using macros to manipulate the data. I thought a little bit more about the approach that might work best for you. Currently you complete all the userforms which populate the dated sheet and automatically populate the "Statistics" sheet. The code to populate the "Statistics" sheet is found in the code module for the "REPORT" form. If, on the same day, you want to change or add some data, you would have to do that through the userforms but you would have to re-enter all the data again not just the changes. If you don't re-enter all the data, it would leave blanks in the dated sheet and the current row in "Statistics" for that flight would be replaced with the blanks. I would like to propose a slightly different approach. Click this link to download your file: https://app.box.com/s/5609oq6fx5i8qy06c05t4b9s4j99r7qv and look at the code for "REPORT", you will see that I removed the code that populates "Statistics" and replaced it with a "Call" command to run the macro which is now in Module 3 so "Statistics" will still be populated automatically as it is now. However, since the macro is now in its own module, you can also run it manually. Let's assume that after you have completed all the userforms you find that you have made an error or that you want to add some missing data. It would be tedious to use the forms to do that because you would have to re-enter all the data not just the changes. What you can do now is make the changes manually directly on the dated sheet (not through the userforms) and then manually run the "UpdateStats" macro in Module 3 or better still, add a button to the "REPORT" sheet and assign the "UpdateStats" macro to. This way the button will be copied to the newly created sheet and this will allow you to manually run the macro from there. What do you think about this approach?

[lauro]

Mumps, yes, I like this approach. I think it is a good idea to be able to work from both the userform and/or "manually". I have checked it and I like it very much. I only have 1 last question to ask you and then I guess this program is ready to be brought to the office and be set to use: in cell AG there is a delay time. Right now it is shown as a mere number. Is it possible to show is at hh:mm? After this I think that all that could be done to make this program work and look good has been done. And I really thank you very much not just for your help in programming but also for your ideas. A fresh look at things really helped.

[Mumps1]

I assume that the delay time is entered manually into "Statistics" sheet. Is this correct?

[lauro]

no, it is inserted via the userform....

[Mumps1]

I noticed that you inserted two new lines of code to copy the Delay Code/Time. Here is the revised macro to handle the time format issue. I inserted the two new lines slightly modified for the row number just above the "else" statement as well. In order for this to work properly, when you enter the Delay Code/Time in the userform, you must enter the time in this format: 00:00. For example, for a delay of one minute, you would enter 00:01; for a delay of one hour and 15 minutes, you would enter 01:15. Please let me know how it works out.

Please Login or Register  to view this content.

[lauro]

Hello Mumps.

I don't understand what is going on but... yesterday when I tried the codes on my pc (excel in English) all worked fine. Today, at the office (excel in Italian) I keep getting an error in this line

Sheets("Statistics").Cells(FirstBlank, "T") = Split(Sheets(Format(Date, "dd-mm-yy")).Range("AD27"), "/")(0)

(that is the first highlighted line in the code).

I would like to send the program to you, but as it now contains lots of data, is there any chance that I can send that to a private email address?

[Mumps1]

Perhaps you could upload a copy of the file to box.com or Dropbox.com and send me the link in a private message. After I download it you could go back and delete it from the upload site.

[Mumps1]

I had a look at your file. The problem is occurring because cell AD27 is blank. The error will also come up if X18 is blank. This version of the macro should fix that problem:

Please Login or Register  to view this content.

Don't forget to enter the delay time in the format 00:00.

[lauro]

Dear Mumps,

thank you so very much for your great help, and your patience. I have tested it on my pc and it really works great.

[Mumps1]

It was my pleasure. I learned much from the process. Buona fortuna.