Dialog Box pauses my process

L

liam.mccartney

Using a macro I'm pasting in a large amount of data from one workbook to
another which is then delimited by comma. Since I'm repeating this process
many times the data delimits over old data previously entered. When this
happens excel asks if I want to overwrite on the destination cells which
pauses the automated process

So my question is: how do I program my macro to tell Excel yes when this
dialog box comes up? It would expedite this quite a bit.

Here is my code as of now:

Dim i As Integer
Dim filearray As Variant

filearray = Application.GetOpenFilename(Title:="(*.epw)", MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
Range("A1:A8768").Select
Selection.Copy
Windows("Analysis truncated.xls").Activate
Sheets("Raw Data").Activate
Range("A1").Select
ActiveSheet.Paste
Selection.TextToColumns
Application.CutCopyMode = False
Windows("Analysis truncated.xls").Activate
Sheets("TMY Analysis 3-6").Range("A2:I2").EntireRow.Copy
Sheets("Weather
Stations").Range("A65536").End(xlUp)(2).EntireRow.PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
Else:
MsgBox "End"
End If

Thank you!
 
E

EricG

Try wrapping your code with these two statements. They turn off certain
warnings in Excel, so you should not see the dialog box. I haven't tried it
for your case, so I'm not 100% sure it will work.

Application.DisplayAlerts = False ' Turn off Excel warnings...
....your code here
Application.DisplayAlerts = True ' Turn them back on when done

HTH,

Eric
 
J

JLGWhiz

Application.AlertBeforeOverwriting = False

'Code to add data

Application.AlertBeforeOverwriting = True
 
E

EricG

I never knew that specific property existed - I learn something new here
every day!

Eric
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top