Recorded Macro doing more than what was recorded

[bartoax]

I have a macro that I recorded to copy columns E-H, and paste values, then delete columns L-R.

The problem that I am having is when I run the macro, instead of pasting values on just E-H, it pastes the values for A-K. I have formulas in some of the other columns that I need to stay as formulas.

Any suggestions on how to fix?

 

[My Aswer Is This]

You said:

copy columns E-H, and paste values​

Are you saying you want to copy all values in column E-H and paste them back in same column E-H but as values not as formula result?

If not where do you want to paste them?

 

[Joe4]

Welcome to the Board!

Please post the code you have recorded so we can see it.

 

[bartoax]

Here is the code:

Sub QUOTE()
'
' QUOTE Macro
'
' Keyboard Shortcut: Ctrl+q
'
Columns("E:H").Select
Range("E2").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("L:R").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("C1").Select
ActiveWindow.View = xlPageLayoutView
End Sub

 

[Joe4]

Try this simplification:

Sub QUOTE()
'
' QUOTE Macro
'
' Keyboard Shortcut: Ctrl+q
'
With Columns("E:H")
    .Value = .Value
End With

Columns("L:R").Delete Shift:=xlToLeft

End Sub

 

[bartoax]

Yes, I am posting them back into E-H just as values not formulas.

I use this sheet as a quote, so columns L-R hold all my private cost etc. Columns E-H are pricing formula calculated from the cells in L-R, so I want to turn those into values before deleting L-R. However I have some formulas that I want to remain in my quote to my customer.
Example: Column E is the per each price, Column J is the extended price so it takes column A (the quantity) and multiplies is by the cost. I want this formula to remain so if the customer adjusted their quantities the pricing will update automatically for them. However, when I run the macro (posted in another reply) is turns A-J into values.

 

[Joe4]

I am guessing that you might have missed my last post as you were posting your reply.
I think that code should do what you want.

 

[bartoax]

That is working, but it is taking the macro a long time to run. Is there anything else I can do to speed that up?

 

[Joe4]

I am guessing that you have a lot of data then.

Try this variation:

Sub QUOTE()
'
' QUOTE Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Dim lRow As Long
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
'   Find last row
    lRow = Range("E1").SpecialCells(xlLastCell).Row
    
    With Range("E1:H" & lRow)
        .Value = .Value
    End With

    Columns("L:R").Delete Shift:=xlToLeft
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[bartoax]

Thank you so much. That fixed it.