VBA Copy Paste Macro - Crashes after c14 loops

[LinVen]

Hi All

I have a macro which does what I want it to, except that it seems to crash after around 14 files. (It used to run fine, and currently needs to complete about 70 files... though that list will grow)

Anyone have any suggestions on how i can improve my code to avoid the "Cannot Paste Data" error that I keep getting?

Option Explicit

Sub get_Skills_data()
Dim Wb1 As Workbook
Dim SourceRow As Long
Dim SourceCol As Long
Dim TargetRow As Long
Dim TargetCol As Long
Dim FileCell As Long
Dim LoopCounter As Integer

'variables defined above, then start points set below

SourceRow = 2
SourceCol = 3
TargetRow = 2
TargetCol = 1
LoopCounter = 0

Application.Calculation = xlManual

Application.DisplayAlerts = False

Worksheets("SKILLS DATA").Visible = True
ActiveWorkbook.Sheets("FileList").Select
Application.ScreenUpdating = False 'to stop flicker as macro works

Do Until IsEmpty(Cells(SourceRow, SourceCol))
        
        Set Wb1 = Workbooks.Open(Cells(SourceRow, SourceCol).Value) 'opens the source data sheet
               
        Wb1.Sheets("FLAT_FILE2").Range("A2:AB8").Copy
     
        Wb1.Close SaveChanges:=False
   
        ActiveWorkbook.Sheets("SKILLS DATA").Select
                 
        Cells(TargetRow, TargetCol).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False     'added in recently to try to avoid crashes, but has not helped
             
       
        TargetRow = TargetRow + 7
        SourceRow = SourceRow + 1
        LoopCounter = LoopCounter + 1
        Application.StatusBar = LoopCounter & " files completed" 'shows progress through the macro
       
        ActiveWorkbook.Sheets("FileList").Select
Loop

ActiveWorkbook.Sheets("03. Skills").Select
[A2].Value = Date + Time 'time-stamp last update
Application.StatusBar = False 'to clear the status bar again
Worksheets("SKILLS DATA").Visible = False
Worksheets("INSTRUCTIONS").Visible = False
MsgBox ("Data Refresh Complete: " & LoopCounter & " Files Copied. Recalculate?") 'to confirm completion of macro

Application.ScreenUpdating = True 'to turn back on normal operation
Application.DisplayAlerts = True '(not needed if not using above)
Application.Calculation = xlAutomatic

End Sub

 

[Joe4]

Does the error always happen on one certain file?
If so, I would recommend focusing in on that file.
Try stepping through your code while running through that one file, and see if there is something in the file that is causing it to bomb out.

 

[LinVen]

No. it is not one specific file which triggers the issue

Sometimes i will just end and try again and it will make it to 10 files, then to 12, then to 14, then to 10 again
then other times i decide to "force it" to move on, by tweaking the Source & Target Rows, to get the next few files, again we manage 10 to 14 files and it crashes

at best it manages 20 files in one go before crashing (I currently have 70 to get through and collate, but will soon have over a hundred)

 

[LinVen]

Also, possibly worth noting that I built the source files (template rolled out to the team - everyone uses one individually)
I know that the format and structure are clean and "right"
I really hoped it was a clipboard issue (too much data) but clearing the clipboard has not helped

 

[Joe4]

Which line of code does it crash on?

I think I would investigate the value of my variables at the time it crashes.

I often see that kind of error message when you are trying to paste something paste the last possible row, last possible column, or are trying to paste in a read-only range.

 

[LinVen]

It consistently crashes on the Paste line

I get the Could not Paste Data error,

It is not pasting to a read only range, nor to the last available row or column....

 

[Joe4]

It is difficult for us to test/debug this without your data files.

Can you tell us what range (exact address) it is trying to copy, and where it is trying to copy to (exact address) when this error occurs?

 

[LinVen]

It is difficult to share something of the actual files, due to the sensitive data contained therein

but from each individual file, we locate the hidden tab labelled FLATFILE and copy A2:AB8

then we close the file

go back to the master/overview

select the Raw Data tab

Paste into A2:AB8

next file in the loop repeats, but Target Row increased (by 7) from 2 to 9, so we paste into A9:AB15, then into A16:AB22 and so on

 

[LinVen]

where exactly it is trying to paste to when it crashes will vary, depending on how many loops it managed before it "got tired and gave up"

it will always be columns A to AB (for this particular macro) but the row range depends on how many files have already completed
There is no one file, nor one location which causes a problem - it can be at any point

 

[Joe4]

where exactly it is trying to paste to when it crashes will vary, depending on how many loops it managed before it "got tired and gave up"

it will always be columns A to AB (for this particular macro) but the row range depends on how many files have already completed
There is no one file, nor one location which causes a problem - it can be at any point

I realize that, but it is often helpful to look at the details of a particular failure, and see if you can see what is going on.

It might be helpful to do the following and see if the problem still occurs.
- Make a "dummy" file (with dummy data that is not sensitive)
- Make 20 copies of this file and put it in a certain folder
- Run the code on that folder

Does the issue still happen?
If it does, then you know that the issue is not with the data files themselves.
And then you can upload a copy of the "dummy" file for us to some file sharing site (like "Dropbox"), where we can download it and test your code against it.