Hi all,

I have a master spreadsheet, which is a number of tabs each with different tables on.

On each tab there is a "button" assigned to a macro, to import data from Excel files saved in a location.

This is an example of one of my codes:

Sub Procedure1()

Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet

Application.ScreenUpdating = False

Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("C:\Users\Christopher.Ellis\Desktop\VBA Testing\CPA 1.xlsm")
Set sht1 = wkb1.Sheets("Data")
Set sht2 = wkb2.Sheets("Sheet6")

sht2.Range("AD27:AJ27").Copy
sht1.Range("B4").PasteSpecial xlFormats

sht2.Range("AD27:AJ27").Copy
sht1.Range("B4").PasteSpecial xlValues

Application.CutCopyMode = False
wkb2.Close True

Application.ScreenUpdating = True

End Sub
The "button" has the following macro assigned to it:

Sub CopyAll()

Procedure1
On Error Resume Next
Procedure2
On Error Resume Next
Procedure3
On Error Resume Next
Procedure4
On Error Resume Next
Procedure5
On Error Resume Next
Procedure6
On Error Resume Next
Procedure7
On Error Resume Next
Procedure8
On Error Resume Next
Procedure9
On Error Resume Next
Procedure10
On Error Resume Next

End Sub
There is the potential of 10 files called CPA1 to 10 in the relevant folder. However, there is often only 2 or 3 of them, hence the use of On Error Resume Next.

This works perfectly fine, but I am moving this file and any corresponding files to a different location so that others users can use it. When I change the location of the files I get an error message when it cant find CPA3, 4 and so on - despite having the On Error Resume Next part of the code. Eventually after pressing ok several times I get the desired output, but this doesn't happen when the file is in its original location.

Any idea why this is now happening?

Many thanks,

Chris