Special copy and paste from one sheet to another

[Choust]

This is a macro i have been using to update update information from one sheet to the next empty row in another sheet within the same workbook.

Sheet2.Unprotect Password:="Climber7c+"
With Sheets("FeedbackForm").Range("B7:N8")
.Copy Destination:=Sheets("Feedback & Performance").Range("A" & Rows.Count).End(xlUp).Offset(1)
.ClearContents
Sheet2.Protect Password:="Climber7c+"

The problem is i don't want to copy the formating, cell protection properties and comments, just the values with in the cells.

Can anyone help me alter my code to perform this?

Thanks,

 

[TheNoocH]

try this...

Sheet2.Unprotect Password:="Climber7c+"
With Sheets("FeedbackForm").Range("B7:N8")
.Copy 
 Sheets("Feedback & Performance").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
.ClearContents
Sheet2.Protect Password:="Climber7c+"

 

[Choust]

That didn't work. I tried quite a few different variations using the .pastespecial command and most of them return either a syntax error or an application runtime error...

Any other suggestions...
Thanks,

 

[TheNoocH]

where is the syntax error?

there wasn't any End With in your original post...i assume this is just part of your overall code?

if you just run this code does this work?

Sub cp()
With Sheets("FeedbackForm").Range("B7:N8")
.Copy
 Sheets("Feedback & Performance").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
.ClearContents
End With
End Sub

 

[Choust]

No that returns "runtime error, Application-defined or object-defined error"

 

[TheNoocH]

so you ran just that sub and got an error?
did it highlight a line?
double check your sheet names and make sure they are the same as what is in the code

 

[Choust]

Yep, just that sub, same sheet names, no protection on the workbook and still the same error....

 

[TheNoocH]

what line in the code does it highlight?

 

[Choust]

Ok i think i may have realised why it is not working....
How do i copy the cell values and also the formating but not the comments?

Thanks for your replies.

 

[Choust]

Paste:=xlPasteValuesAndNumberFormats ?