Marco - After the calculation is done, I wish to paste the values in the calculated rows, but preserve the formulas in the rows below

Question

Here is what I have so far. It's a macro to run a text to columns of pasted material. This is then pulled into another sheet that has vlookups. After the v-look up successfully pulls the formula I want to copy paste special value those. But not the cells below it in columns D through J that have vlookups to pull formulas for data copied later. Please assist. I am very new at macros.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+v
'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
        True
    Sheets("Bags").Select
    Columns("D:J").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Welcome to SO. Your question is pretty unclear. Please read [How to Ask](http://stackoverflow.com/help/how-to-ask) to receive the most effective help on the site. — Scott Holtzman, Feb 01 '16 at 17:58

score 0 · Answer 1 · edited May 23 '17 at 12:04

I take it you store some data in column A, do the text to columns part and then copy-paste only the rows in Column D:J where there actually is data in column A:C. In other words: After the calculation is done, you wish to paste the values in the calculated rows, but preserve the formulas in the rows below.

The problem lies in the fact that you do not determine the last row that holds the data, so your selection will encompass only those cells. Instead you just copy paste the entire columns.

The example below will leave the entered formulas in Columns D:J intact for rows that don't have data in Column A:C.

Sub Macro1()

Dim lastRow as integer 'We need a variable to store the row number in.

'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+v
'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
        True

'Let's assume this first part works correctly, since your question does not address this. 
'However - Keep in mind that the text to columns have a destination that will always be A1 in your example.

    lastRow = Sheets("Bags").Range("A65535").end(xlUp).Row 'Determine the last row in column A that has a value in it.
    Sheets("Bags").Range("D2:J" & lastRow).Select 'Select only the rows that have values in column A.
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Comments on this answer: This will fix your immediate problem, however it is still not a very clean way to do it. Also I do not think the above code will do exactly what you want. The question answered is: How to edit your code, so it will now overwrite the formulas in D:J in rows that have no data in A.

Other notes / pointers: Next: If possible try to avoid selections at all. There's a great question + asnwers as to how and why in an old threat here: How to avoid using Select in Excel VBA macros

Also I recommend reading up on how to reference ranges / cells:

https://msdn.microsoft.com/en-us/library/office/aa221357(v=office.11).aspx

http://excelmatters.com/referring-to-ranges-in-vba/

http://spreadsheetpage.com/index.php/tip/referring_to_ranges_in_your_vba_code/

Knowing how this works is a good start when new to Macro's. Good luck!

Marco - After the calculation is done, I wish to paste the values in the calculated rows, but preserve the formulas in the rows below

1 Answers1