VBA code is removing formula from cells after copy and paste command

Question

I have the following code which is working as I would expect in regards to it is copying my row of data and pasting into a new row.

It then clears the data from the original row ready for the next input.

The only problem is that it is removing all my formulas even though I have stated which cells are to be cleared in my code.

Sub CopyPaste()

Range("A9:Q9").Copy

Sheets("Raw data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 Range("A9:Q9").Select
    Range("Q9").Activate
    
    Selection.ClearContents
    Range("A9").Select
    Selection.ClearContents
    Range("B9").Select
    Selection.ClearContents
    Range("L9").Select
    Selection.ClearContents
    Range("M9").Select
    Selection.ClearContents
    Range("P9").Select
    ActiveWindow.ScrollColumn = 2
    Range("A9").Select
   
End Sub

Probably something ridiculously obvious.

First step: get rid of the `Select`ing, see [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jul 09 '20 at 14:38
your first `Selection.ClearContents` is clearing `Range("A9:Q9")` as that is what is selected. — Scott Craner, Jul 09 '20 at 14:42
^^^^^^^^ [`Range.Activate`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.activate) is different than `Range.Select`, but you should be doing neither. — BigBen, Jul 09 '20 at 14:43

score 2 · Accepted Answer · answered Jul 09 '20 at 14:46

Range.Activate is not the same as Range.Select, but you should be doing neither of those; change

Range("A9:Q9").Select
    Range("Q9").Activate
    
    Selection.ClearContents
    Range("A9").Select
    Selection.ClearContents
    Range("B9").Select
    Selection.ClearContents
    Range("L9").Select
    Selection.ClearContents
    Range("M9").Select
    Selection.ClearContents
    Range("P9").Select
    ActiveWindow.ScrollColumn = 2
    Range("A9").Select

to

   Range("Q9").ClearContents
   Range("A9").ClearContents
   Range("B9").ClearContents
   ... and so on

See How to avoid using Select for a thorough discussion of how to improve your code.

score -1 · Answer 2 · answered Jul 09 '20 at 14:40

-1

You are telling it to paste values... not formulas.

Try using xlPasteAll instead of xlPasteValues

answered Jul 09 '20 at 14:40

douglas.kirschman

136
1
7

Hi, thanks for responding. When the paste is performed the data is being pasted into a new row in a separate worksheet (worksheet B) which I do not want the formulas in. However when I press my macro button after the paste has occurred (and this part works fine it is going onto the new row as required) I then want to clear certain cells from the main data entry worksheet (worksheet A), this is where it is going wrong as all cells are clearing instead of just the selecting which in turn is removing my formulas. – L Rogers Jul 09 '20 at 14:43

VBA code is removing formula from cells after copy and paste command

2 Answers2