0

I think this is a very easy fix for those that know VBA macros well. For some reason adding .value at the end creates a runtime error.

Could you, please, help and let me know how to use the copy method of column B, but instead of copying the formula in the original inputs sheet, I could copy the value only.

Sub StatusPointBuild()
'
' StatusPointBuild Macro
'

'
    Dim y As Long

    ThisWorkbook.Sheets("Status Output").Cells.ClearContents
    Worksheets("Status Template").Range("B2:DW2").Copy Worksheets("Status Output").Range("B2:DW2")

    'only copy the number of times as the count in cell A1
    y = Sheets("inputs").Range("A1").Value
    Worksheets("Status Template").Range("B3:DW3").Copy Worksheets("Status Output").Range("B3:DW3").Resize(y)
    Range("B3:DW90").Select

    Do Until IsEmpty(ActiveCell)

        Worksheets("inputs").Range("B:B").Copy Worksheets("Status Output").Range("B:B")
        ActiveCell.Offset(1, 0).Select

    Loop

    Sheets("Status Output").Activate

End Sub

Thank you

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
ksenia
  • 187
  • 7
  • 23
  • 2
    See this question https://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues – JamesFaix Jan 26 '18 at 19:19
  • In particular see the answer by Robotik – chris neilsen Jan 26 '18 at 19:59
  • thank you @JamesFaix. However, updating the copy command to Worksheets("inputs").Columns(2).Copy Worksheets("Status Output").Columns(2).PasteSpecial xlPasteValues puts me in an infinite loop – ksenia Jan 26 '18 at 21:30
  • How many rows are you dealing with and how complicated are the formulas? – JamesFaix Jan 26 '18 at 21:56
  • @JamesFaix, it is a simple copy column from one worksheet to another and I am dealing with 24 rows, but only specifying one example in the code above. Only column B. – ksenia Jan 26 '18 at 22:21

0 Answers0