Copy data from SAP exported file errors after few iterations

Question

I've got an issue about exporting data from SAP to Excel. After few copy/paste my code stops and can't find either mother file or exported file . Is there any way to delete this issue?. On my own I can always fix it by just clicking "F5" or clicking mother/exported files and after that "F5". But want to let this code run automatically so it can't have any errors.

It can happen on second copy or third, sometimes never happens.

ostatni_transport = Workbooks(ten_plik).Worksheets("TR").Cells(1, 1).End(xlDown).Row

    Workbooks("Arkusz w ALVXXL01 (1)").Sheets(1).Range(Cells(2, 1), Cells(Cells(1, 1).End(xlDown).Row, 7)).Copy
    Workbooks(ten_plik).Worksheets("TR").Cells(ostatni_transport + 1, 1).PasteSpecial xlPasteValues 'A-G
    Workbooks("Arkusz w ALVXXL01 (1)").Sheets(1).Range(Cells(2, 8), Cells(Cells(1, 1).End(xlDown).Row, 8)).Copy
    Workbooks(ten_plik).Worksheets("TR").Cells(ostatni_transport + 1, 10).PasteSpecial xlPasteValues 'J
    Workbooks("Arkusz w ALVXXL01 (1)").Sheets(1).Range(Cells(2, 10), Cells(Cells(1, 1).End(xlDown).Row, 10)).Copy
    Workbooks(ten_plik).Worksheets("TR").Cells(ostatni_transport + 1, 8).PasteSpecial xlPasteValues 'H
    Workbooks("Arkusz w ALVXXL01 (1)").Sheets(1).Range(Cells(2, 11), Cells(Cells(1, 1).End(xlDown).Row, 11)).Copy
    Workbooks(ten_plik).Worksheets("TR").Cells(ostatni_transport + 1, 9).PasteSpecial xlPasteValues 'I

Is there any way to fix it? I can't change overlay of data, that's why I've got to copy and paste it a few times.

I tried to specify cells more accurately with workbook&worksheet before cells - sadly didn't help.

Your question seems to be only about Excel VBA. Could you explain the symptom concerning "my code stops"? Which line? Which error message? — Sandra Rossi
– Sandra Rossi, Commented Sep 8, 2021 at 8:48
Hi, My question is semi-connected with SAP, since downlaoding data exported from SAP with this method can allways give this error. The error occures sometimes in last line of code, sometimes in the middle of code (pasted in Question). The error code is "Run-time error 1104" - Method PasteSpecial from class Range failed. After clicking "debug" and then "F5" sometimes fixes this issue, sometimes i've got to select my main file or downloaded file and then go back to code -> click "F5" and bug is fixed. Fixing it is easy, but I need to delete this bug completly. — p77u77n77k
– p77u77n77k, Commented Sep 8, 2021 at 9:21

CDP1802 · Accepted Answer · 2021-09-15 16:15:20Z

1

Try without copy/paste

    Dim wsSAP As Worksheet, ws As Worksheet
    Dim n As Long, ostatni_transport As Long

    Set wsSAP = Workbooks("Arkusz w ALVXXL01 (1)").Sheets(1)
    Set ws = Workbooks(ten_plik).Worksheets("TR")
    ostatni_transport = ws.Cells(1, 1).End(xlDown).Row
    
    n = wsSAP.Range("A1").End(xlDown).Row - 1 'A-G
    ws.Range("A" & ostatni_transport + 1).Resize(n, 7).Value2 = wsSAP.Range("A2").Resize(n, 7).Value2

    n = wsSAP.Range("H1").End(xlDown).Row - 1 '
    ws.Range("J" & ostatni_transport + 1).Resize(n).Value2 = wsSAP.Range("H2").Resize(n).Value2

    n = wsSAP.Range("J1").End(xlDown).Row - 1
    ws.Range("H" & ostatni_transport + 1).Resize(n).Value2 = wsSAP.Range("J2").Resize(n).Value2
 
    n = wsSAP.Range("K1").End(xlDown).Row - 1
    ws.Range("I" & ostatni_transport + 1).Resize(n).Value2 = wsSAP.Range("K2").Resize(n).Value2

edited Sep 15, 2021 at 16:15

answered Sep 8, 2021 at 10:13

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CDP1802 Over a year ago

@p77u77n What error did you get and on which line ?

p77u77n77k Over a year ago

on first line of ws.range..... couldn't copy/paste values with "range=range"

p77u77n77k · Accepted Answer · 2021-09-15 09:48:30Z

With help of previous answer did mange to find solution to this issue.

For now working fine - did test it ~20 times.

Dim wsSAP As Worksheet, ws As Worksheet
Dim n As Long, ostatni_transport As Long

Set wsSAP = Workbooks("Arkusz w ALVXXL01 (1)").Sheets(1)
Set ws = Workbooks(ten_plik).Worksheets("TR")
ostatni_transport = ws.Cells(1, 1).End(xlDown).Row

n = wsSAP.Range("A1").End(xlDown).Row 
Set kopia = wsSAP.Range("A2").Resize(n, 7)
kopia.Copy Destination:=ws.Range("A" & ostatni_transport + 1).Resize(n, 7) 'A-G

Set kopia = wsSAP.Range("H2").Resize(n)
kopia.Copy Destination:=ws.Range("J" & ostatni_transport + 1).Resize(n) 'J

Set kopia = wsSAP.Range("J2").Resize(n)
kopia.Copy Destination:=ws.Range("H" & ostatni_transport + 1).Resize(n) 'H

Set kopia = wsSAP.Range("K2").Resize(n)
kopia.Copy Destination:=ws.Range("I" & ostatni_transport + 1).Resize(n) 'I

Collectives™ on Stack Overflow

Copy data from SAP exported file errors after few iterations

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related