Clever Geek Handbook

Vba excel how to insert values ​​without font / color / bg formatting

I have a macro to copy a summary row from each series of sheets. The summary line is specially formatted with color / bg, but when inserted into the "summary sheet" it should just insert the values ​​without formatting.

For LoopIndex = StartIndex To EndIndex
    ' start in a task sheet
    Sheets(LoopIndex).Select
    CopiedCells = ActiveSheet.Range("A156:L156").Copy

    ' now move to Summary sheet
    Sheets("Summary Sheet").Select
    ActiveSheet.Range("A8").Select
    ActiveCell.EntireRow.Insert

    ActiveCell.PasteSpecial Paste:=xlPasteValues
    ' tried variations of: ActiveCell.PasteSpecial paste:=xlValues, operation:=xlPasteSpecialOperationNone

    Application.CutCopyMode = False ' clears clipboard
Next LoopIndex

All research done says that PastSpecial, xlValues, xlPasteValues ​​should work, but it doesn't format anything, I don't know what I'm doing wrong here. It inserts values, not reference values, so that's fine. I have a macro to reset the formatting in a loop, but I would like to make it more efficient. I am using Excel 2007.

+2


source to share


3 answers


This is really weird!

The reason is that you copy, paste and then paste. Try pasting, copying and then pasting:

'we must commence on the Summary Sheet
Sheets("Summary Sheet").Select
For LoopIndex = StartIndex To EndIndex

    ' insert the row before we start
    ActiveSheet.Range("A8").Select
    ActiveCell.EntireRow.Insert

    ' select the task sheet
    Sheets(LoopIndex).Select
    CopiedCells = ActiveSheet.Range("A156:L156").Copy

    ' now move to Summary sheet
    Sheets("Summary Sheet").Select

    ActiveCell.PasteSpecial Paste:=xlPasteValues
    ' tried variations of: ActiveCell.PasteSpecial paste:=xlValues, operation:=xlPasteSpecialOperationNone

    Application.CutCopyMode = False ' clears clipboard
Next LoopIndex


For what it's worth, I had copy and paste issues. This means that while your macro is running, you cannot do any more.

Since this is a fixed range, I would suggest the following:

For LoopIndex = StartIndex To EndIndex
    Sheets("Summary Sheet").Range("A8").EntireRow.Insert
    For i = 1 To 12
        Sheets("Summary Sheet").Cells(8, i) = Sheets(LoopIndex).Cells(156, i)
    Next
Next
+4


source 


ActiveSheet.Range("A1").EntireRow.Copy
ActiveSheet.Range("A2").EntireRow.PasteSpecial xlPasteValues
Application.CutCopyMode = False

or



ActiveSheet.Range("A2").EntireRow.Value = ActiveSheet.Range("A1").EntireRow.Value

Replace A1 with your source and A2 with your target.

+4


source


Once I have selected a range, I put this 

 Selection.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone

Works great for me :)

+3


source






More articles:


All Articles