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" <(E-Mail Removed)> wrote in message
news:60979784-0210-4BB8-937C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:60979784-0210-4BB8-937C-(E-Mail Removed)...
> > 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!
> >
>
>
>