0

I have written some code that copies data from one workbook to another - using share point as file path. It works on all machines expect on one computer, the user is getting a Run-time error '9' (see below). I assume it cant be a file path issue as it works on others machines. I Thought perhaps if I changed the code around but does same thing that it would work - but it didn't...You can see the code I tried as it is commented out. Could it be a setting on the user machines/excel doc. Or is there other code I should try?

Thanks for all help

Private Sub CommandButton1_Click()

Dim x As Workbook
Dim y As Workbook
'Dim ws1 As Worksheet, ws2 As Worksheet'
Set x = Workbooks.Open("filepath.xlsx")
Set y = Workbooks.Open("filepath.xlsm")
'y.Sheets("Data").Range("A2") = x.Sheets("Form1").Range("C2:I1000")
x.Sheets("Form1").Range("C2:I1000").Copy
y.Sheets("Data").Range("A2").PasteSpecial
x.Close
 'Set ws1 = x.Sheets("Form1")
 'Set ws2 = y.Sheets("Data")

 'ws1.Range("C2:I1000").Copy: ws2.Range("A2").PasteSpecial xlPasteValues
 'x.Close True
 'y.Close False

Call dropdown

End Sub

It has a problem with this line of code (debug):

    y.Sheets("Data").Range("A2").PasteSpecial

Error message:

Run-time error '9':

Subscript out of range

Sneftel
  • 40,271
  • 12
  • 71
  • 104
Donald
  • 17
  • 5
  • Have You tried just: ws1.Range("C2:I1000").Copy ws2.Range("A2") – Teamothy Sep 06 '19 at 12:51
  • 4
    That specific error code means that the object specified cannot be found. I bet that the sheet "Data" does not exist. – Tim Stack Sep 06 '19 at 12:52
  • @TimStack It does exist because it works on all other machines, and I checked that first - It is all on share-point so everyone has the same file path - I have ensured that I have linked the file path to his file directory? So from your response I assume it is not code but perhaps another technicality in his setup. – Donald Sep 06 '19 at 12:55
  • @Teamothy No but I will try it now, thanks – Donald Sep 06 '19 at 12:56
  • How about looping through the sheets in workbook `y` and printing all sheet names? Perhaps there is something odd about the sheet name. I advise you to use codenames instead though, which is [easily doable](https://stackoverflow.com/a/22256782/10540017) if this is a static sheet. – Tim Stack Sep 06 '19 at 13:00
  • Is the spec of the failing machine *very* different to the others? Copy/Paste by VBA can sometimes become unstable on very slow memory machines if the Paste is called before the Copy has completed (though not normally for such a relatively small number of cells). If it's a slow machine, you could try adding a `DoEvents` after the copy line to help this ? – CLR Sep 06 '19 at 14:27
  • If one of the files you're working with is already open then that can cause some issues. Make sure they're all closed before running your code. https://stackoverflow.com/questions/56780454/can-having-multiple-instances-of-excel-open-cause-vba-issues/56781317#56781317 – Tim Williams Sep 06 '19 at 15:02

0 Answers0