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