Copy Values instead of formulas in an offset copy

Question

I am having trouble figuring out how to have my offset copy and paste code, copy values rather than formulas.

I have the following code which works perfectly but putting the copied cells in the next blank column, but I need the values, not formulas pasted in.

Sub Burndown_Snapshot()

'Copies the Overall Status Summary Data from the Dashboard and adds to the next empty column of the Historic Status table
'Triggered by the 'Burndown Snapshot' button on the dashboard


Dim column_number As Integer

column_number = Sheets("Historic Status").Cells.Find(What:="*", After:=Range("IV65536"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
Sheets("Dashboard").Range("C3:C7").Copy Destination:=Sheets("Historic Status").Cells(1, column_number)

End Sub

I figure i need a .PasteValues in there somewhere but im not sure where

Thanks

Answer 1

Copy Column Range

Set lCell = dws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious)

• dws.Cells means all worksheet cells e.g. A1:XFD1048576, or A1:IV65536 for version prior to Office 2007.
• The 2nd (omitted) argument (of the Find method) is After whose default parameter is Cells(1) or Cells("A1") i.e. the first cell of whichever range (dws.Cells) you apply the method to.
• Combined with the xlPrevious parameter of the SearchDirection argument, it looks first in the last cell (e.g. XFD1048576).
• By choosing the xlByColumns parameter of the SearchOrder argument, the next cell looked in will be XFD1048575 etc.
• The xlFormulas parameter of the LookIn argument makes sure that the first non-empty cell is found (not ="" or "'", just empty (blank includes all three)).
• The parameter of the 4th, the LookAt argument, is irrelevant and therefore omitted.

Set dCell = dws.Cells(1, lCell.Column + 1)

• After the cell is found (Not lCell Is Nothing), we reference the cell (dCell)in the first row (1 before the comma) in lCell's column adjacent to the right (lCell.Column + 1)

Set drg = dCell.Resize(srg.Rows.Count, srg.Columns.Count)

• The destination range has to be the same size as the source range to copy by assignment.
• In this particular case, you can safely omit , srg.Columns.Count since there is only one column Set drg = dCell.Resize(srg.Rows.Count) (the default parameter of both arguments of the Resize method is 1.
• Now you can copy values by assignment: drg.Value = srg.Value.

Sub Burndown_Snapshot()
'Copies the Overall Status Summary Data from the Dashboard and adds to the next empty column of the Historic Status table
'Triggered by the 'Burndown Snapshot' button on the dashboard

    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Dashboard")
    Dim srg As Range: Set srg = sws.Range("C3:C7")
    
    Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets("Historic Status")
    Dim lCell As Range
    Set lCell = dws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' no data in range
    Dim dCell As Range: Set dCell = dws.Cells(1, lCell.Column + 1)
    Dim drg As Range: Set drg = dCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    drg.Value = srg.Value

End Sub