MsgBox Error

[jreed5535]

Hi Guys,

I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2.

The only difference is the MsgBox goes into capitals

any ideas why this is happening?

Cheers

 

[jreed5535]

Another question, is there away i can put in a code to click exit on the pop up box when opening a file? like the below.

thanks again

 

[Michael M]

Something like this will do it
UpdateLinks:=0 won't update
UpdateLinks:=1 will update

Set mybook = Workbooks.Open(Filename:=MyPath & MyFiles(FNum), UpdateLinks:=0)

 

[jreed5535]

Thanks Michael, works perfect!
I have one last code i am trying to do but cannot figure it out.

I want to insert a blank row 2 rows under a cell if that cell meets a criteria (i will need to link the criteria to a cell in a different sheet as it will change) then copy and paste the row above, then copy and values paste a value from a cell in another worksheet.

So want it so find ..17MINE then insert a row 2 rows under it.

it will find where to insert the row based on D2 value
then copy the formulas from the above cell (could maybe just copy the row 1 below the ..17MINE then insert copied cell)
then values paste B2 into a cell in the row just added.

Hope this makes sense.

Thanks again

 

[Michael M]

this will put a insert the row 2 rows below

Sub MM1()
Dim r As Long
For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row
   If Cells(r, 4).Value = "..17MINE" Then Cells(r + 2, 4).EntireRow.Insert
Next r
End Sub

 

[jreed5535]

is there anyway to have it link to a cell in another worksheet instead of Value = "..17MINE"?

 

[Michael M]

Sorry, I don't follow ???

 

[jreed5535]

so have something like this

If Cells(r, 4).Value = worksheets ("sheet1"). range("D2"). Then Cells(r + 2, 4).EntireRow.Insert

So if D2 changes it will look for that value instead.

 

[Michael M]

Like this ??

Sub MM1()
Dim r As Long, ws As Long
Set ws = Sheets("Sheet1")
For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row
If Cells(r, 4).Value = ws.Cells(2, 4).Value Then   Cells(r + 2, 4).EntireRow.Insert
Next r
End Sub

 

[jreed5535]

this comes up?

 

[Michael M]

OOPs, sorry.
Change the 2nd line to

Dim r As Long, ws As Worksheet