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!
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!