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

CodePudding user response：

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

CodePudding user response：

So I updated my code to include an extra copy paste function to give me the result I want, which also works, but requires a defined cut and paste range (which in most cases should be workable)

    Sub Burndown_Snapshot2()

'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

Application.ScreenUpdating = False
Dim column_number As Integer
Dim rng As Range

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)


For Each rng In Sheets("Historic Status").Range("B1:ZZ5")
       If rng.Value <> "" Then
            rng.Value = rng.Value
        End If
   Next rng
    
Application.ScreenUpdating = False


End Sub

However @VBasic2008s answer is much cleaner and doesn't have an arbitrary end point