Currently I'm using a user form to add data to a Specific sheet in my workbook. Ive been tasked to change the way the data is entered. It now needs to transfer to a specific week tab 1-52 …Fiscal year
For an example 5 text boxes *Date* *Category* *total hrs* *New* *Completed* after all boxes are filled click enter and data transfer to specific tab that matches the date, but in a fiscal year.
Code below currently transfer to one sheet with only 4 fields, I need to add the date textbox or a calendar. I feel it could be easier, but this is what they want. I'm open for suggestions and all help is appreciated!
I also have a formula that tells me what week number the date is, not sure if that will help...
Thank You!
For an example 5 text boxes *Date* *Category* *total hrs* *New* *Completed* after all boxes are filled click enter and data transfer to specific tab that matches the date, but in a fiscal year.
Code below currently transfer to one sheet with only 4 fields, I need to add the date textbox or a calendar. I feel it could be easier, but this is what they want. I'm open for suggestions and all help is appreciated!
VBA Code:
Dim Lastrow As Long, ws As Worksheet
Set ws = Sheets("WorkOrder") ' this needs to be changed to look for week that's between the date given
Lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("B" & Lastrow).Value = Reg1.Text 'Adds the TextBox3 into Col A & Last Blank Row
ws.Range("C" & Lastrow).Value = Reg2.Text
ws.Range("D" & Lastrow).Value = Reg3.Text
ws.Range("E" & Lastrow).Value = Reg4.Text
Me.Reg1.Value = ""
Me.Reg2.Value = ""
Me.Reg3.Value = ""
Me.Reg4.Value = ""
Unload Me
SQL:
=IF(((WEEKNUM(B5)-WEEKNUM(DATE(2020,7,1)))+1)<0,(WEEKNUM(DATE(2020,7,1))+WEEKNUM(B5))-1, (WEEKNUM(B5)-WEEKNUM(DATE(2020,7,1)))+1)
Thank You!