victorel21
New Member
- Joined
- Jul 8, 2021
- Messages
- 25
- Office Version
- 365
- Platform
- Windows
I am getting a copy paste error as it seems nothing is being pasted, could anyone help with this.
This worked fine yesterday, but today it isnt.
A
Sub todatabase2()
Dim ID, GR, Departamento, Semana, Comentarios, Month As Range
With Sheets("Form")
ID = Range("F1")
GR = Range("A3")
Departamento = Range("C3")
Semana = Range("D3")
Comentarios = Range("B25")
End With
Set Month = Sheets("Datalist").Cells(Range("E:E").Find(Semana, Lookat:=xlWhole).Row, 6)
With Sheets("Employee_List")
Set RngCol = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
LastRow = RngCol.Rows.Count
.Range("A2:G" & LastRow + 1).Copy
End With
With Sheets("Historico")
last_line = .Range("F" & Rows.Count).End(xlUp).Row + 1
.Range("F" & last_line).PasteSpecial Paste:=xlPasteValues
Do While Cells(last_line, 6) <> ""
If .Range("F" & last_line) <> "" Then
.Range("A" & last_line) = ID
.Range("B" & last_line) = GR
.Range("C" & last_line) = Departamento
.Range("D" & last_line) = Semana
.Range("M" & last_line) = Comentarios
.Range("E" & last_line) = Month
End If
last_line = last_line + 1
Loop
End With
ThisWorkbook.Worksheets("Historico").Cells.EntireColumn.AutoFit
End Sub
This worked fine yesterday, but today it isnt.
A
Sub todatabase2()
Dim ID, GR, Departamento, Semana, Comentarios, Month As Range
With Sheets("Form")
ID = Range("F1")
GR = Range("A3")
Departamento = Range("C3")
Semana = Range("D3")
Comentarios = Range("B25")
End With
Set Month = Sheets("Datalist").Cells(Range("E:E").Find(Semana, Lookat:=xlWhole).Row, 6)
With Sheets("Employee_List")
Set RngCol = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
LastRow = RngCol.Rows.Count
.Range("A2:G" & LastRow + 1).Copy
End With
With Sheets("Historico")
last_line = .Range("F" & Rows.Count).End(xlUp).Row + 1
.Range("F" & last_line).PasteSpecial Paste:=xlPasteValues
Do While Cells(last_line, 6) <> ""
If .Range("F" & last_line) <> "" Then
.Range("A" & last_line) = ID
.Range("B" & last_line) = GR
.Range("C" & last_line) = Departamento
.Range("D" & last_line) = Semana
.Range("M" & last_line) = Comentarios
.Range("E" & last_line) = Month
End If
last_line = last_line + 1
Loop
End With
ThisWorkbook.Worksheets("Historico").Cells.EntireColumn.AutoFit
End Sub
Overtime Request Form.V6 historic by employee ID.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | ID | Nombre | Departamento | Type | Total Horas Extra | Costo Horas | Comida | ||
2 | 46 | Junior Alexander Santana Ye | Canvas BD3 | DIR | 18.00 | 26.53 | 14.48 | ||
3 | 52 | Francisco Antonio Rondon | Canvas BD3 | DIR | 22.50 | 33.16 | 18.10 | ||
4 | 1915 | Marilin Avila Mariano | Canvas BD4 | DIR | 22.50 | 33.16 | 18.10 | ||
5 | 55 | Wilmer Jimenez Avila | Canvas BD4 | DIR | 18.00 | 26.53 | 14.48 | ||
6 | 65 | Orangel Andres Constanzo Jimenez | Canvas BD4 | DIR | 22.50 | 33.16 | 18.10 | ||
7 | 70 | Reyito Millord Dishmey | Maintenance | INDIR | 22.50 | 43.25 | 18.10 | ||
Employee_List |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B7 | B2 | =IF(A2="","",XLOOKUP(A2,Employees_Data!C:C,Employees_Data!A:A)& " " &XLOOKUP(A2,Employees_Data!C:C,Employees_Data!B:B)) |
C2:C7 | C2 | =IF(A2="","",VLOOKUP(A2,Employees_Data!C:E,3,0)) |
D2:D7 | D2 | =IF(A2="","",VLOOKUP(A2,Employees_Data!C:H,6,0)) |
E2:E7 | E2 | =IF(A2="","",IF(H2="YES",Calc!$G$2,0)+IF(I2="YES",Calc!$G$3,0)+IF(J2="YES",Calc!$G$4,0)+IF(K2="YES",Calc!$G$5,0)+IF(L2="YES",Calc!$G$6,0)+IF(M2="YES",Calc!$G$7,0)+IF(N2="YES",Calc!$G$8,0)) |
F2:F7 | F2 | =IF(Employee_List!$D2="","",IF(H2="YES",INDEX(Calc!$A$1:$K$8,2,MATCH("Cost"&" "&Employee_List!D2,Calc!$1:$1,0)))+IF(I2="YES",INDEX(Calc!$A$1:$K$8,3,MATCH("Cost"&" "&Employee_List!D2,Calc!$1:$1,0)))+IF(J2="YES",INDEX(Calc!$A$1:$K$8,4,MATCH("Cost"&" "&Employee_List!D2,Calc!$1:$1,0)))+IF(K2="YES",INDEX(Calc!$A$1:$K$8,5,MATCH("Cost"&" "&Employee_List!D2,Calc!$1:$1,0)))+IF(L2="YES",INDEX(Calc!$A$1:$K$8,6,MATCH("Cost"&" "&Employee_List!D2,Calc!$1:$1,0)))+IF(M2="YES",INDEX(Calc!$A$1:$K$8,7,MATCH("Cost"&" "&Employee_List!D2,Calc!$1:$1,0)))+IF(N2="YES",INDEX(Calc!$A$1:$K$8,8,MATCH("Cost"&" "&Employee_List!D2,Calc!$1:$1,0)))) |
G2:G7 | G2 | =IF(Employee_List!$D2="","",IF(AND(H2="YES",INDEX(Calc!$A$1:$M$8,2,8)="YES"),Datalist!$C$5,0)+IF(AND(I2="YES",INDEX(Calc!$A$1:$M$8,3,8)="YES"),Datalist!$C$5,0)+IF(AND(J2="YES",INDEX(Calc!$A$1:$M$8,4,8)="YES"),Datalist!$C$5,0)+IF(AND(K2="YES",INDEX(Calc!$A$1:$M$8,5,8)="YES"),Datalist!$C$5,0)+IF(AND(L2="YES",INDEX(Calc!$A$1:$M$8,6,8)="YES"),Datalist!$C$5,0)+IF(AND(M2="YES",INDEX(Calc!$A$1:$M$8,7,8)="YES"),Datalist!$C$5,0)+IF(AND(N2="YES",INDEX(Calc!$A$1:$M$8,8,8)="YES"),Datalist!$C$5,0)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Employees_Data!_FilterDatabase | =Employees_Data!$A$1:$J$378 | B2:B7 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A:A | Cell Value | duplicates | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
A2:A7 | List | =OFFSET(Employees_Data!C2,0,0,COUNTA(Employees_Data!C:C)-1) |