+ Reply to Thread
Results 1 to 4 of 4

Pasting data without the worksheet automatically delimiting it?

  1. #1
    Registered User
    Join Date
    08-05-2005
    Posts
    2

    Pasting data without the worksheet automatically delimiting it?

    Hi All,

    Quick question about Text to Columns function/pasting data. Basically i've created a Macro which involves using Text to Columns to delimit some data. The macro is associated with a command button and is working fine.
    The problem is that when I close and reopen the worksheet with the command button in it and paste my data in, it automatically delimits it before I run the macro. This renders the macro useless as its dependant on the data being pasted in, in a certain format.
    The only way I can get the sheet to accept the pasted data as normal (not delimit it automatically) is to close down excel completely and then reopen my worksheet. Is there a way to 'reset' the worksheet before I paste the data in, or some other way around this?
    Any help would be greatly appreciated!!

    Niall

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    I'm not sure how this may help, if you paste data, then Text to Columns on that data, then paste some more, and more on another sheet, your new pastes go at the format that your last Text to Columns was set for.

    If you then format (any other odd couple of cells) and remove all delimiters you can then paste data that is no longer formatted, ie, retains it's Notepad format into column A etc.

    Perhaps the end of your macro could 'unset' or set to nothing, any delimiters.

    Otherwise a manual Text to Columns of a couple of cells to manually unset the format that was retained might be needed.


    Quote Originally Posted by NiallC
    Hi All,

    Quick question about Text to Columns function/pasting data. Basically i've created a Macro which involves using Text to Columns to delimit some data. The macro is associated with a command button and is working fine.
    The problem is that when I close and reopen the worksheet with the command button in it and paste my data in, it automatically delimits it before I run the macro. This renders the macro useless as its dependant on the data being pasted in, in a certain format.
    The only way I can get the sheet to accept the pasted data as normal (not delimit it automatically) is to close down excel completely and then reopen my worksheet. Is there a way to 'reset' the worksheet before I paste the data in, or some other way around this?
    Any help would be greatly appreciated!!

    Niall

  3. #3
    Dave Peterson
    Guest

    Re: Pasting data without the worksheet automatically delimiting it?

    If you do a dummy data|text to columns (delimited by nothing), then this'll
    reset the settings.

    You could add something like this to the bottom of your macro that does the
    data|text to columns:

    Dim DummyCell As Range
    With ActiveSheet
    Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
    With DummyCell
    .Value = "asdf"
    .TextToColumns Destination:=.Cells, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, _
    Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    .ClearContents
    End With
    End With



    NiallC wrote:
    >
    > Hi All,
    >
    > Quick question about Text to Columns function/pasting data. Basically
    > i've created a Macro which involves using Text to Columns to delimit
    > some data. The macro is associated with a command button and is working
    > fine.
    > The problem is that when I close and reopen the worksheet with the
    > command button in it and paste my data in, it automatically delimits it
    > before I run the macro. This renders the macro useless as its dependant
    > on the data being pasted in, in a certain format.
    > The only way I can get the sheet to accept the pasted data as normal
    > (not delimit it automatically) is to close down excel completely and
    > then reopen my worksheet. Is there a way to 'reset' the worksheet
    > before I paste the data in, or some other way around this?
    > Any help would be greatly appreciated!!
    >
    > Niall
    >
    > --
    > NiallC
    > ------------------------------------------------------------------------
    > NiallC's Profile: http://www.excelforum.com/member.php...o&userid=25948
    > View this thread: http://www.excelforum.com/showthread...hreadid=393246


    --

    Dave Peterson

  4. #4
    Registered User
    Join Date
    08-05-2005
    Posts
    2
    Bryan, Dave,
    Thanks a lot for your help on this, much appreciated.....
    Niall

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1