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!

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

"liam.mccartney" wrote:

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.

Wow, that worked beautifully!

Thank's so much.



"EricG" wrote:

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

"liam.mccartney" wrote:

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.

Application.AlertBeforeOverwriting = False

'Code to add data

Application.AlertBeforeOverwriting = True


"liam.mccartney" wrote in message
...
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!

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

Eric

"JLGWhiz" wrote:

Application.AlertBeforeOverwriting = False

'Code to add data

Application.AlertBeforeOverwriting = True


"liam.mccartney" wrote in message
...
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!