VBA Copy Paste Macro - Crashes after c14 loops

LinVen

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?


VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

LinVen

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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)

:(
 
Upvote 0

LinVen

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

LinVen

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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....
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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?
 
Upvote 0

LinVen

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

LinVen

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
1,186,769
Messages
5,959,650
Members
438,440
Latest member
Hi577

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top