Part of the code below copies a column to a separate tab (column P) to perform text to column process (Town -CenterStreet-20) for (columns P, Q & R) I do this to separate out the Town name with the "-" as a delimited separator, the code then copies only the town column and pastes it back into the working file.
The VBA is stopping to due the columns Q & R having data from the last time this process was run for the previous month. How do I alter the code to say Yes to the box question that appears to overwrite columns Q & R?
The VBA is stopping to due the columns Q & R having data from the last time this process was run for the previous month. How do I alter the code to say Yes to the box question that appears to overwrite columns Q & R?
VBA Code:
Range("O1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Selection.Copy
Sheets("Original report ").Select
ActiveWindow.ScrollColumn = 1
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Town Location"