Macro halts on 2nd Paste command
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Macro halts on 2nd Paste command

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've tried this multiple ways and it always stops running on the second "Paste". There's no error, it just stops. I am attempting to copy and then paste a column, looping through the columns on a spreadsheet until there is no more data. Please tell me if you can figure out why I am unable to Paste more than once. Here's my code, followed by a test method that also halts:
    Public Sub Main()
    Dim colNext As Integer
    Dim strTest As String

    On Error GoTo ErrorHandler

    'Start at the first column of participant data
    colNext = 3

    'Set the Loop condition to the first SSN
    strTest = Data.Cells(3, colNext).Text

    Do Until (strTest = "")

    'Copy the next column of data
    Data.Columns(colNext).Select
    'Application.CutCopyMode = False
    Selection.Copy
    'Paste the data to the referenced column
    'Data.Columns(2).Select
    Data.Paste Data.Columns(2)

    'Print out Results Tab
    'This works, but I don't want to print every test run
    'Results.PrintOut
    MsgBox ("Printing " & Data.Cells(2, colNext))

    'Get ready for next loop
    colNext = colNext + 1
    strTest = Data.Cells(3, colNext).Text
    Loop

    'Temporary code - Showing exit conditions of Loop
    strMsgBox = "Exiting macro, with colNext = " & colNext & _
    ", strText = " & strTest & "."
    intMsgBox = MsgBox(strMsgBox, vbOKOnly, "Exiting Macro")

    Exit Sub

    ErrorHandler:

    MsgBox ("Error: " & Err.Number & ", " & Err.Description)

    End Sub

    Sub Test()
    'Copy the next column of data
    Data.Columns(3).Copy
    Data.Paste Data.Columns(2)

    'Copy the next column of data
    Data.Columns(4).Copy
    Data.Paste Data.Columns(2)

    'Copy the next column of data
    Data.Columns(5).Copy
    Data.Paste Data.Columns(2)

    'Copy the next column of data
    Data.Columns(6).Copy
    Data.Paste Data.Columns(2)

    'Copy the next column of data
    Data.Columns(7).Copy
    Data.Paste Data.Columns(2)

    'Copy the next column of data
    Data.Columns(8).Copy
    Data.Paste Data.Columns(2)

    End Sub

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using the following:

    Columns(2).Select
    ActiveSheet.Paste

    Instead of your paste code.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I wish it were that easy! I've tried that and several other roundabout ways of pasting.
    However, I actually DID get it to work just a second ago, using this code:

    Do Until (strTest = "")

    'Clear the referenced data column
    Data.Columns(2).Clear
    'Copy the next column
    Data.Columns(colNext).Copy (Data.Columns(2))

    'Print out the Results Tab
    Results.PrintOut

    'Get ready for next loop
    colNext = colNext + 1
    strTest = Data.Cells(3, colNext).Text

    Loop

    Apparently you have to clear the column if you are repeatedly pasting to it. So far that's the only solution I've found that works.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com