0

Below the code I wrote. It works in debug mode, but crashed when I tried to run the macro. I guess it relates to selection command, but I couldn't figure out the exact issue.

Can someone help? Thanks!

Sub copyemployeeTCdata()

Dim e_row, e_row2, e_row3 As Integer
Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("merg")

Dim setting_sh As Worksheet
Set setting_sh = ThisWorkbook.Sheets("chart")
Set pt = setting_sh.PivotTables("PivotTable1")
e_row = pt.TableRange1.Rows.Count - 1

setting_sh.Range("R:R").Clear
setting_sh.Range("S:S").Clear

e_row2 = 0
For i = 1 To e_row
data_sh.Activate
On Error Resume Next
    data_sh.ShowAllData

data_sh.UsedRange.AutoFilter 22, setting_sh.Range("A" & 57 + i).Value

Set CopyTable = data_sh.Range("AE:AE")

Set CopyTable = CopyTable.Resize(CopyTable.Rows.Count - 1)
Set CopyTable = CopyTable.Offset(1)
CopyTable.Copy
'CopyTable.Select

setting_sh.Activate
'calculate last row -erow2 in column S
e_row2 = setting_sh.Cells(Rows.Count, 19).End(xlUp).Row
If e_row2 < 56 Then

setting_sh.Cells(57, 19).Select
ActiveSheet.Paste
e_row3 = setting_sh.Cells(Rows.Count, 19).End(xlUp).Row
setting_sh.Range(Cells(57, 18), Cells(e_row3, 18)).Value = i

Else
setting_sh.Cells(e_row2 + 1, 19).Select
Selection.PasteSpecial transpose:=False
e_row3 = setting_sh.Cells(Rows.Count, 19).End(xlUp).Row
setting_sh.Range(Cells(e_row2 + 1, 18), Cells(e_row3, 18)).Value = i
End If
Next i
End Sub

Thanks!

Jean
  • 1
  • 1
    I would start by removing every instance of `Activate`, `Select`, & `Selection` as these just consume memory and slow your process down for no good reason. Please see [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to update – urdearboy Mar 23 '21 at 21:47
  • 1
    You should declare your row variables using `Long` to allow for the max row size. Also, your `Dim` statement is not doing what you think it is. You have declared `e_row3 as Integer` while the remaining two variables (`e_row` & `e_row2`) have defaulted to `Variant` since they are not declared correctly. Replace that line with `Dim e_row as Long, e_row2 as Long, e_row3 as Long` – urdearboy Mar 23 '21 at 21:49
  • 1
    **Lastly:** remove the line `On Error Resume Next`. This just hides errors from you which is not helpful when you are trying to debug. Remove that line and the re-run your code. It will hopefully produce a RTE and tell you the exact line that caused the issue – urdearboy Mar 23 '21 at 21:50
  • 1
    A lot of errors here. Your `CopyTable` paste alone will give you an error since you are trying to paste an entire column (1 mil rows) to a range that doesn't provide you with enough rows. You need to limit your copy to the last row. Otherwise, your paste needs to start at row 1. So, a lot to work on! GL :) – urdearboy Mar 23 '21 at 21:55

0 Answers0