Previous Topic Next Topic

Create new sheet and copy data from input page to the new page

Closed
IMR4N Posts 9 Registration date Thursday December 5, 2013 Status Member Last seen January 14, 2014 - Dec 5, 2013 at 04:17 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 14, 2014 at 11:26 AM
Hello Every One,

I am a basic excel user. I am trying to create an excel data base that will keep a record of employee duty hours, dates and types of duty all inserted in the first sheet, may be from a drop down list. Employee name can also be selected from a drop down list. I want excell to create a new sheet each time a new employee is selected and add his/her hours with dates and other information to that sheet.

Is that even possible?



5 responses

Answer 1 / 5
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 5, 2013 at 11:14 AM
Sure IMR4N, but more info is required. What needs to go where, where are the DDL's located etc..

Consider uploading your file using a filesharing site like www.speedyshare.com or ge.tt. Then provide the download link in your next post.

Please make sure it is clear what you want to achieve in great detail.

Best regards,
Trowa
0
Answer 2 / 5
IMR4N Posts 9 Registration date Thursday December 5, 2013 Status Member Last seen January 14, 2014
Dec 8, 2013 at 01:44 AM
Hello TrowaD,

Thank you very much for your quick response and sorry at the same time for me being so late. Link to my file is
[code]http://speedy.sh/Yt4Pb/FDTL.xlsm[/code]

My requirement is to add a calender to be added on the first sheet from which the user can choose a date. (i know its very easy with Excel 2007/2013, i use 2003)

As you can see from the attached file that the flight number origin and destination is chosen from a drop down list same as the name of the pilots. Flight number, and pilots names are unique. For third pilot null data is valid but not for the Captain and co-pilot. Block and block off time will be entered by the user in 24 hours format. time can start on one day and end on the next.

I want a sheet for each pilot to be created when his name is selected for the first time. Flight number, date, origin and destination and flight time (difference between block off and block on) will be saved on the specific page for all three pilots.

I hope i am clear enough

Regards

Imran
0
Answer 3 / 5
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 10, 2013 at 11:19 AM
Hi Imran,

1) You say you use Excel 2003, yet you have a .xlsm extension?

2) In the past I have used a userform as a calendar substitute, but where do you want the date if the time is in the Block On/Off cells?

3) You want a sheet to be created for each pilot. That means you want to create 3 sheets when 3 different names are selected for the first time?

4) You need to create a Pilot Template sheet, so I know where you want the info placed.

5) The Enter button is not working? Is that there for me to assign the code to?

6) I sounds weird to me that you only want to keep the information the first time a pilot is entered.

So shine some light on my questions, adjust your workbook and re-upload your file with the .xls extension (I'm also using Excel 2003).

Best regards,
Trowa
0
Answer 4 / 5
IMR4N Posts 9 Registration date Thursday December 5, 2013 Status Member Last seen January 14, 2014
Dec 11, 2013 at 01:07 AM
Hi Trowa,

1. Sorry for creating all the confusion. I have excel 2007 and i meant the date feature not available in 2007 like the later versions. I had 2003 before, later upgraded to 2007, i just forgot that i did so.

2. The date can be anywhere in the sheet, like a button that will open up a calender to select date from. I guess a user form will work too.

3. Yes, I want a sheet to be created for each pilot, that means 3 sheets for the first time three names are selected.

4. The sheet for the pilots individual data may include a header (pilots name). The data inserted/selected from the first sheet may come below arranged according to date (latest on top) like follows:


PILOTS NAME


DATE FLIGHT ORIGIN DESTINATION FLIGHT TIME

8/12/13 XXX XXX XXX 3:20
3/12/13 xxx xxx xxx 6:05
1/12/13 XXX XXX XXX 2:30



5. YES, ENTER button is for the code to be assigned to

6. I hope my ans to 4 clarified this query. I want all the information entered for the pilot to be recorded, not for the first time only. Sorry again for the confusions, English is not my first language.

The file in .xls format: http://speedy.sh/PJW4v/FDTL.xls


Thank you very much for your time and effort.

Regards

Imran
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 12, 2013 at 11:36 AM
OK Imran, thanks for the clarification.

I've got the calendar going, just need to check a few things with you.

1) It will be easier to create all the sheets for all the pilots at once. Anything against that?
They could be hidden and then unhidden the first time the pilot is selected, but in the end all pilots will be selected at one point or another, right? Please clarify the reason.

2) We will be having 2 dates, right? On for the start of the flight and one for the end.

3) On the Pilot sheet which date are you referring to? I'm guessing the start date, but I want to be sure.

4) Flight time = block off - block on, correct? Just find it strange you placed block off before block on.

Best regards,
Trowa
0
IMR4N Posts 9 Registration date Thursday December 5, 2013 Status Member Last seen January 14, 2014
Dec 12, 2013 at 01:44 PM
Dear Trowa,

Your welcome and glad that i could finally clarify the problem. Here are your answers:

1. Yes we can create pages for each pilot right now. But like any other employments, pilots also dont stay at the same company for long time. They come and go. So creating pages may serve the purposes now but when a new pilot joins the company, you'll face the trouble of recording his data again.

2. You are correct. We really need 2 dates.


3. Start date on pilots' page

4. Correct again, Flight time = blocks on - blocks off

My mistake


Regards


Imran
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 17, 2013 at 12:06 PM
Hi Imran,

1) Didn't think about new pilots, good point.

4) Wow, that sounds confusing to me. So the start time is called block off and the end time is called block on, that's good to know.

Last thing to do for me is to figure out how to display flight time in a correct way.

I'll get back to you on Thursday.

Kind regards,
Trowa
0
IMR4N Posts 9 Registration date Thursday December 5, 2013 Status Member Last seen January 14, 2014
Dec 18, 2013 at 01:42 AM
Hi Trowa,

Its not confusing actually. Chocks off/Blocks off is the term used in aviation for the time wheel starts rolling, that is, when the Chock/Block placed in front of the wheel is removed (and hence blocks off). Chock/block is again placed after aircraft comes back to the ramp before the engine is switched off. This is considered the end time and hence blocks on. Got it?

Regards

Imran
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 19, 2013 at 10:50 AM
Hi Imran,

Yep I got it, thanks for explaining, it makes perfect sense now.

Here is your file, just click on the "Enter" button and see if the desired result shows:
http://speedy.sh/qKz72/IMR4N-FDTL-V2.xls

I wish you a cosy Christmas and a glorious new year!

Best regards,
Trowa
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jan 14, 2014 at 11:11 AM
Hi Imran,

You say there can be 2 flights with the same flight number on the same day, so checking flight number and blocks off won't be enough, right?
Instead the below code will check if there is already an identical entry based on Flight number, Origin and Destination.
Replace the entire code placed under Module1 with the following one: 
Sub UpdatePilotPages()
Dim ws As Worksheet
Dim PN1, PN2, PN3, sDate, Flight, Origin, Desti As String
Dim lRow As Integer
Dim x, y As Double
Dim FlightTime As Date


PN1 = Range("F9").Value
PN2 = Range("K9").Value
PN3 = Range("P9").Value
sDate = Range("H12").Value
Flight = Range("F6").Value
Origin = Range("K6").Value
Desti = Range("P6").Value
FlightTime = (DateDiff("h", Range("H12"), Range("M12")) / 24) + (Range("M14") - Range("H14"))

If PN1 <> vbNullString Then
    For Each ws In Worksheets
        If Sheets(ws.Name).Name = PN1 Then x = 1
    Next ws
    
    If x = 1 Then
        Sheets(PN1).Activate
        
        lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        
        For Each cell In Range("B" & lRow - 1)
            If cell.Value = Flight And cell.Offset(0, 1).Value = Origin And cell.Offset(0, 2).Value = Desti Then
                MsgBox "Flight details for Captain " & PN1 & " has already been submitted."
                y = 1
            End If
        Next cell
        
        If y <> 1 Then
            Range("A" & lRow) = sDate
            Range("B" & lRow) = Flight
            Range("C" & lRow) = Origin
            Range("D" & lRow) = Desti
            Range("E" & lRow) = FlightTime
        End If
        
    Else
        Sheets("PilotTemplate").Visible = True
        
        Sheets("PilotTemplate").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = PN1
        
        Range("C1") = PN1
        lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        Range("A" & lRow) = sDate
        Range("B" & lRow) = Flight
        Range("C" & lRow) = Origin
        Range("D" & lRow) = Desti
        Range("E" & lRow) = FlightTime
        
        Sheets("PilotTemplate").Visible = False
        
    End If
End If

If PN2 <> vbNullString Then
    x = 0
    y = 0
    For Each ws In Worksheets
        If Sheets(ws.Name).Name = PN2 Then x = 1
    Next ws
    
    If x = 1 Then
        Sheets(PN2).Activate
        
        lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        
        For Each cell In Range("B" & lRow - 1)
            If cell.Value = Flight And cell.Offset(0, 1).Value = Origin And cell.Offset(0, 2).Value = Desti Then
                MsgBox "Flight details for Co-Pilot " & PN2 & " has already been submitted."
                y = 1
            End If
        Next cell
        
        If y <> 1 Then
            Range("A" & lRow) = sDate
            Range("B" & lRow) = Flight
            Range("C" & lRow) = Origin
            Range("D" & lRow) = Desti
            Range("E" & lRow) = FlightTime
        End If
        
    Else
        Sheets("PilotTemplate").Visible = True
        
        Sheets("PilotTemplate").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = PN2
        
        Range("C1") = PN2
        lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        Range("A" & lRow) = sDate
        Range("B" & lRow) = Flight
        Range("C" & lRow) = Origin
        Range("D" & lRow) = Desti
        Range("E" & lRow) = FlightTime
        
        Sheets("PilotTemplate").Visible = False
        
    End If
End If

If PN3 <> vbNullString Then
    x = 0
    y = 0
    For Each ws In Worksheets
        If Sheets(ws.Name).Name = PN3 Then x = 1
    Next ws
    
    If x = 1 Then
        Sheets(PN3).Activate
        
        lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        
        For Each cell In Range("B" & lRow - 1)
            If cell.Value = Flight And cell.Offset(0, 1).Value = Origin And cell.Offset(0, 2).Value = Desti Then
                MsgBox "Flight details for 3rd Pilot " & PN3 & " has already been submitted."
                y = 1
            End If
        Next cell
        
        If y <> 1 Then
            Range("A" & lRow) = sDate
            Range("B" & lRow) = Flight
            Range("C" & lRow) = Origin
            Range("D" & lRow) = Desti
            Range("E" & lRow) = FlightTime
        End If
        
    Else
        Sheets("PilotTemplate").Visible = True
        
        Sheets("PilotTemplate").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = PN3
        
        Range("C1") = PN3
        lRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        Range("A" & lRow) = sDate
        Range("B" & lRow) = Flight
        Range("C" & lRow) = Origin
        Range("D" & lRow) = Desti
        Range("E" & lRow) = FlightTime
        
        Sheets("PilotTemplate").Visible = False
        
    End If
End If

Sheets("Data Entry").Activate
MsgBox "The pilot sheets have been updated.", vbInformation, "Confirmation"

End Sub

I also want to remind you that all Pilot sheets are created from the hidden sheet called PilotTemplate. This means that you only have to format and apply codes to this one sheet, for all newly added Pilot sheet to have the same format and codes. No need to format each Pilot sheet individually like it looks like you are doing now.

All the best,
Trowa
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jan 14, 2014 at 11:26 AM
I saw that I missed your request to sort the Pilot sheets.
The best we can do here (imo) is to sort data based on date first and flight number second. If you agree, use the following code on the Pilot sheets you already have and the PilotTemplate sheet: 
Private Sub Worksheet_Activate()
Dim lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A4:E" & lRow).Sort Key1:=Range("A4"), Order1:=xlDescending, Key2:=Range("B4"), Order2:=xlDescending
End Sub
0

Similar discussions

Hi Guys how to create an account for facebook with my company em
Ramu -
Ahmadzai -

2 responses
Input phone lock code
Lucas -
M.Mudasir -

3 responses
Yahoo account reactivation
Senkumba -
kitkat55 -

2 responses
Why can't I bridge connect?
ryno -
Qiqi -

15 responses
input phone password
djzombo -
Rahul -

2 responses