Copy paste special error

victorel21 · Jul 8, 2021

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

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

3

52

Francisco Antonio Rondon

4

1915

Marilin Avila Mariano

5

55

6

65

Orangel Andres Constanzo Jimenez

7

70

Reyito Millord Dishmey

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)

DanteAmor · Jul 8, 2021

I would really have to do tests with the data in your sheets to see what is happening. You could put a sample with minisheets from your other sheets.

I anticipate that some dots are missing in your references:

Rich (BB code):

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(Sheets("Datalist").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

victorel21 · Jul 8, 2021

This is the main form

Overtime Request Form.V6 historic by employee ID.xlsm

C

D

E

6

Hora Llegada

Hora Partida

Comida

7

16:30:00

21:00:00

YES

8

16:30:00

21:00:00

YES

9

16:30:00

21:00:00

YES

10

16:30:00

21:00:00

YES

11

16:30:00

21:00:00

YES

12

13

Form

Cells with Data Validation
Cell	Allow	Criteria
E7:E13	List	=List!$D$2:$D$3

Here I do some calcs

Overtime Request Form.V6 historic by employee ID.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

DIA

Hora Llegada

Hora Partida

Horas Extras

Horas extras dobles

Horas nocturnas

Total Horas

Comida

Cost Dir

Cost Indir

Cost Sup

2

LU:

3

MA:

4

MIE:

5

JUE:

6

VIE:

7

SA:

-

8

DO:

-

Calc

This is the end result

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

3

52

Francisco Antonio Rondon

4

1915

Marilin Avila Mariano

5

55

6

65

Orangel Andres Constanzo Jimenez

7

70

Reyito Millord Dishmey

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)

Other sheets are just databases where the vlookups come from.

victorel21 · Jul 8, 2021

Looks like the error was due to the sheet being protected, I tried adding a line to unprotect but it didnt help, the protection was automatic from a code in the worksheet but for some reason unprotecting didnt help. I will try different things and determine the exact reason as I need it protected.

victorel21 · Jul 8, 2021

Hi I found the solution to be, to unprotect and protect in the same module, I no longer have the errors.

Copy paste special error

victorel21

New Member

Excel Facts

DanteAmor

Well-known Member

victorel21

New Member

victorel21

New Member

victorel21

New Member

Similar threads

Forum statistics

Share this page

Copy paste special error

victorel21

New Member

Excel Facts

DanteAmor

Well-known Member

victorel21

New Member

victorel21

New Member

victorel21

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock