Speed up copied data from one Excel file and paste it to another one

[Jirka79]

Hi,

I have the attached code that copies the data from one excel file and copy it to another one. This data is daily increased, so when I copy it, then needs to be paste it starting from the last time that was updated.

I have the feeling that is taking too much time and maybe there is way to speed it up. Could somebody please take a look and give me some advice?

Thank you all in advance!!

Sub transferDATA()

Dim StRo As Integer, T As Integer, Ro2 As Integer, Lr As Integer

If Range("AA1").Value = 1 Then

Application.ScreenUpdating = False
Application.EnableEvents = False
Workbooks("Production data.xlsm").Worksheets("Production").Activate
ActiveSheet.Unprotect Password:="123"
With Sheets("Production")
M = Workbooks("All data.xlsm").Worksheets("TransferedDATA").UsedRange.Rows.Count

If Workbooks("All data.xlsm").Worksheets("TransferedDATA").UsedRange.Rows.Count = 1 Then
.Range("A4:Z4").Copy Workbooks("All data.xlsm").Worksheets("TransferedDATA").Range("A4")
StRo = .Range("AA:AA").Find("X").Row
Lr = 4
Else
Lr = Workbooks("All data.xlsm").Worksheets("TransferedDATA").Range("L" & Rows.Count).End(xlUp).Row
StRo = .Range("L:L").Find(Workbooks("All data.xlsm").Worksheets("TransferedDATA").Range("L" & Lr)).Row + 1
End If

For T = StRo To .Range("A" & Rows.Count).End(xlUp).Row
    If .Range("AA" & T) = "X" Then
    Ro2 = Ro2 + 1
    Workbooks("All data.xlsm").Worksheets("TransferedDATA").Range("A" & Lr + Ro2 & ":Z" & Lr + Ro2).Value = .Range("A" & T & ":Z" & T).Value
    End If
      
Next T

End With
Application.ScreenUpdating = True
Application.EnableEvents = True

Workbooks("All data.xlsm").Worksheets("TransferedDATA").Activate
Else
Exit Sub
End If

End Sub

 

[Jirka79]

Try changing the Clng(Filter.. lines to CDbl(Filter... as per Alex's suggestion in post #16 and see if that gives the correct result.

Unfortunately, I'm about to head overseas for a couple of weeks so I'll only be accessing Mr Excel remotely (away from my laptop). If the above suggestion doesn't work, hopefully @Alex Blakenburg will be able to assist you further (thanks Alex )
Kind regards
Kevin

Yes!! It finally worked as expected with the CDbl!!

Thank you all Kevin, Alex and Mumps for your time and collaboration! )

PD: Have a safe trip Kevin!

 

[kevin9999]

Yes!! It finally worked as expected with the CDbl!!

Thank you all Kevin, Alex and Mumps for your time and collaboration! )

PD: Have a safe trip Kevin!

Alex deserves the credit for this one. Glad it worked and thanks for the feedback.

 

[smozgur]

Yes!! It finally worked as expected with the CDbl!!

It looks like the final working code, with CDbl adjustment, is post #15. Therefore, I switched the marked solution accordingly.

@Jirka79, in your future questions, please mark the post as the solution that answered your question best to help future readers. No further action is required for this one.