0

My issue:

I have a Macro, which sets my worksheet up to convert text into columns automatically, whenever I activate the sheet...

However when I have pasted the data, and move back and forth between the sheets, it will say “There’s already data here. Do you want to replace it?”

The macro on the sheet:

Private Sub Worksheet_Activate()

Dim sht As Worksheet
Dim LastRow As Long
Set sht = Sheets("Kvik kontoudtog")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
rng1 = "A1:A" & LastRow

sht.Range(rng1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))

End Sub

Goal:

I would like the macro to auto “Cancel” the alert, so that I don't have to

Question:

Can I insert a string to automatically click cancel, so it won’t ruin my data, and get rid of the alert by VBA?

CptGoodar
  • 303
  • 2
  • 15
Patrick S
  • 325
  • 2
  • 12
  • Try putting `Application.DisplayAlerts = False` at the top of your sub and `Application.DisplayAlerts = True` at the bottom – CallumDA Jan 04 '18 at 10:25
  • This will just allow the alert to replace it... Which will ruin the data. – Patrick S Jan 04 '18 at 10:28
  • Wasn't sure what the default behaviour was when hiding alerts. I don't think you've got many options here. – CallumDA Jan 04 '18 at 10:29
  • I've figured it out somehow. I changed the ranged it should do the TextToColumn from rng1 to "A1"... Somehow this does the trick. Thanks for your time anyways :) – Patrick S Jan 04 '18 at 10:42

0 Answers0