Hello. I have attached a spreadsheet that I am having an issue with a copy and paste command button. I am trying to copy all of the information in both column A & C, and then clear all the information from just column C. Here is the code I am using.

'Copy the contents of the worksheet onto the clipboard
Dim strCopy As String 'Text from worksheet to be copied
Dim lngRow As Long 'Current working row for data copy

'Build the text string to be copied
With wksSheet 'With Sheet1
For lngRow = 1 To 69 Step 1 'For each of the data lines
strCopy = strCopy & .Range("a" & lngRow) & " " & .Range("c" & lngRow) & vbCrLf 'Copy to strCopy
Next lngRow 'Next row
End With 'End with Sheet1

With New MSForms.DataObject 'With a new Data Object
'Copy the string to the clipboard
Call .SetText(strCopy) 'Set the text to our string
Call .PutInClipboard 'Put the string on the clipboard
End With

'Clear the values
Call Range("c3,c5,c7,c9,c11,c13,c15,c17,c19,c21,c23,c25,c27,c29,c31,c34,c35,c36,c38,c42
,c43,c44,c50,c51,c52,c58,c68,c69").ClearContents

I am getting an error with this code. Does anyone see what needs to be changed. It has worked with my other spreadsheets. Thank you.

What error are you getting? Is it a compile or a run time error? On what line of code is the error occurring? More information please..

It's a run time error on this line:

strCopy = strCopy & .Range("a" & lngRow) & " " & .Range("c" & lngRow) & vbCrLf 'Copy to strCopy

Thank you

Can you post ALL your code? That can't be all the code, and you can remove the word "call" from in front of most lines. instead of Call Range, just start that line with "Range". Same with inside the With statement, you should remove the word "call" there. The word Call is to call an outside procedure, and even then it's not really necessary as long as you have decent naming conventions that dont' conflict w/reserved words.

I don't see a declaration that defines wksSheet, so there must be other code.

This post has been edited by ipisors: Apr 17 2012, 11:00 AM

Oh, ok. Didn't know I could remove them.
I have this code in sheet 1: Call modUtility.CopyValues(Sheet1)
I have this code in this workbook: Me.Saved = True

Sorry but this isn't giving the full picture - can you post the sample workbook? Also what is the actual run time error you're getting?

What I ended up doing is creating a separate copy and separate clear functions. They seem to work independently. The users will just have to make an extra click.

I thank you for your time!