VB Code Help with Excel.

[Faithless]

Hi all,

I need a copy command in an Excel sheet that copies the cell contents not the cell formating.

At the minute, when the active sheet is chosen and its range Range("A1") for instance. I want JUST the cell contents to be copied.

The cell in question as a Orange border around it and that seems to copy over too. This, I do not need, as said, just the cell contents.

Thanks

 

[suarve]

Why not copy the cell, paste just the values into another cell, do whatever you need with it and then delete the newly created cell. E.g. A very quick one that copy say column A1, pastes the values into Column L and then deletes column L. I'm sure there's a much better way of doing it, but meh.

Sub meh()

Columns("A:A").Select
	Selection.Copy
	Range("L1").Select
	Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
		:=False, Transpose:=False
		' Code to do something with the stuff you have just copied
		Columns("L:L").Select
		Selection.Delete Shift:=xlToLeft
		Range("A1").Select
End Sub

 

[Faithless]

Thanks, All I needed to do was use the paste special command and choose value.

Another thing. The two sheets I'm pasting between are both password protected. As it stands now, in order to paste between the two sheets I have to manually enter the password twice. Is there a way to get around the manual passworld typing?

Thanks again

 

[Dan2kx]

record a macro of you unprotecting the sheets, copying and pasting, and then lock the pages again. then you will have the code in a new module which you can modify at will

 

[Meeko]

This should do what your looking for...

ActiveSheet.Unprotect "xxxxxx"
...YOUR CODE
...YOUR CODE
...YOUR CODE
ActiveSheet.Protect "xxxxxxx"

(Where "xxxxxx" is the password)