method run of object _global failed

[marc005]

https://dl.dropboxusercontent.com/s...gZfZaDuLS8155L0_q_kJZxHcX3GEKKkk02tToeyg&dl=1

This Merge1 procedure is really buggy. Sometimes it works and sometime it dont.
Just recently the error message was:

"method run of object _global failed"

How can I solve that? Also does there exist away so I dont have to specify the complete path ie
C:\Users\marc\Desktop\VBA\W to the named ranges in this workbook?

Public Sub Workbook_Open()
Application.DisplayAlerts = False
Run "ThisWorkbook.CorrectDate"
Run "ThisWorkbook.Merge1"
Run "ThisWorkbook.SaveAndCloseAndCSV1"
End Sub


Sub CorrectDate()

Worksheets("USA (NYSE)").Select
Lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To Lastrow Step 1
If Mid(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 7, 1) = 0 Then
Cells(i, 1).Value = Mid(Cells(i, 1).Value, 1, Len(Cells(i, 1).Value) - 8) & "" & Right(Cells(i, 1).Value, 7)
Else
End If
Next i

Worksheets("USA (Nasdaq)").Select
Lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To Lastrow Step 1
If Mid(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 7, 1) = 0 Then
Cells(i, 1).Value = Mid(Cells(i, 1).Value, 1, Len(Cells(i, 1).Value) - 8) & "" & Right(Cells(i, 1).Value, 7)
Else
End If
Next i

Worksheets("USA (ETFs)").Select
Lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To Lastrow Step 1
If Mid(Cells(i, 1).Value, Len(Cells(i, 1).Value) - 7, 1) = 0 Then
Cells(i, 1).Value = Mid(Cells(i, 1).Value, 1, Len(Cells(i, 1).Value) - 8) & "" & Right(Cells(i, 1).Value, 7)
Else
End If
Next i

End Sub


Public Sub SaveAndCloseAndCSV1()

Application.DisplayAlerts = False

Worksheets("Merge").Cells(1, 1).Select

ActiveSheets.SaveAs Filename:= _
"C:\Users\marc\Dropbox\Merge.csv", FileFormat:=xlCSV, CreateBackup:=False

Worksheets("Merge").Cells.ClearContents
ActiveWorkbook.Save

Application.Quit

End Sub



Sub Merge1()

   Worksheets("Merge").Cells(1, 1).Select
   
   z1 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!HongKong"
   z2 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!UK"
   z3 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Brazil"
   z4 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Canada"
   z5 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!China.Shanghai"
   z6 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!China.Shenzhen"
   z7 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!France"
   z8 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Germany"
   z9 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!India"
   z10 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Italy"
   z11 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Japan"
   z12 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Spain"
   z13 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Australia"
   z14 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Sweden"
   z15 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!Turkey"
   z16 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!USA.ETFs"
   z17 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!USA.Nasdaq"
   z18 = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!USA.NYSE"
   
Selection.Consolidate Sources:=Array(z1, z2, z3, z4, z5, z6, z7, z8, z9, z10, z11, z12, z13, z14, z15, z16, z17, z18), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

End Sub

 

[WinteE]

Dim UsersPath as String

UsersPath = "'C:\Users\marc\Desktop\VBA\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'"

z1 = UsersPath & "!Hongkong"
etcetera

 

[marc005]

ok thanx where does the error come from?

 

[Norie]

Why are you using Run?

Try this.

Public Sub Workbook_Open()
Application.DisplayAlerts = False
   Call CorrectDate
   Call Merge1
   Call SaveAndCloseAndCSV1
End Sub

 

[marc005]

May I ask what is the difference? I am no expert just trial and error.

 

[Norie]

Not sure about a difference but you shouldn't need to use Run to call subs that are in the same workbook as the code calling them.

Run is usually only used to call subs in another workbook.

Also, when you use it the first part should be the name of the workbook.

 

[marc005]

ok thanx Norie

 

[marc005]

Norie, does there exist away so I dont have to specify the complete path to the named ranges ie

"'C:\Users\marc\Desktop\<acronym title="visual basic for applications">VBA</acronym>\Wall Street Data Center Downloader - Closing Price\Merg.xlsm'!HongKong"

It creates a lot of problem for me. In a perfect world I could just call named ranges HongKong.
</pre>