Macro halts on 2nd Paste command

cowartlk

New Member
Joined
Apr 7, 2002
Messages
2
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try using the following:

Columns(2).Select
ActiveSheet.Paste

Instead of your paste code.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top