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

[Closed]
Report
Posts
10
Registration date
Thursday December 5, 2013
Status
Member
Last seen
January 14, 2014
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
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 replies

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
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
Posts
10
Registration date
Thursday December 5, 2013
Status
Member
Last seen
January 14, 2014

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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
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
Posts
10
Registration date
Thursday December 5, 2013
Status
Member
Last seen
January 14, 2014

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
Posts
10
Registration date
Thursday December 5, 2013
Status
Member
Last seen
January 14, 2014

Hi Trowa

Hope you had a wonderful vacation.

Please check the file i have uploaded. I have included calculations for the cumulative hours on the pilots page ( first point in the above message). Now I need a warning message if those exceeds some preset value. i tried a small script on individual pages which did not work. I hope you can make them work.

http://speedy.sh/bkytM/IMR4N-FDTL-V3.xls

Besides the second and third point in the previous message, can it be done that the latest entries of data will be arranged on top of the older records o the pilots page?


Best Regards

Imran
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
Hi Imran,

Vacation was great, hopefully yours was as well.

You have protected your file and sheets with passwords.
Either provide password or re-upload your file without protection.

Best regards,
Trowa
Posts
10
Registration date
Thursday December 5, 2013
Status
Member
Last seen
January 14, 2014

Hi Trowa,

Good to know that you enjoyed your holidays. This is not the time for vacation at this part of the world. However, i was busy with my job and it was good too.

I have re-uploaded the file and sorry about the passwords.

http://speedy.sh/qdEx2/IMR4N-FDTL-V3.xls

Regards
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
Hi Imran,

Sorry to hear you vacation was denied. Hopefully you will get it soon.

1) When you change the cell property of a time cell back to standard, you will see the actual number Excel is calculating with. That number will be the time divided by 24.
So your line:
If Range("J3") > 12
should be:
If Range("J3") * 24 > 12

2) Your Data Entry sheet is still protected.
You can create a dropdown list on it and use the following code to jump to it's selected sheet.
Code needs to be implemented in the Data Entry sheet.
Just change the Range("A1") to whatever range you chose to put the DDL in.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
On Error GoTo ErrMessage
Sheets(Target.Value).Activate
Exit Sub
ErrMessage:
MsgBox "Sheet doesn't exist", vbExclamation
End Sub

3) Need some more time for this one. Basically checking for flight number will be enough to determine a duplicate, right?

Best regards,
Trowa
Posts
10
Registration date
Thursday December 5, 2013
Status
Member
Last seen
January 14, 2014

Hi Trowa!

Thanks for your nice words.

i will try the first 2 and let you know.

About 3, flight number should do the trick, but it may happen that a flight has diverted to alternate destination due to some unavoidable reasons and later to the original destination. So in that case there will be 2 flights with the same flight number on same date. I guess you have to take into consideration more than one parameter like flight number and blocks off for avoiding duplicacy.

Regards

Imran
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
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