How to copy an entire sheets values as static into another sheet using vba-CodePudding

I am trying to take a sheet like: A3,B3,C3 are the sum of the 2 values above them.

Copy this entire sheet into another sheet with only static values ie the sum formulas are gone and 5,73,55 are just the values.

Public Sub CopyEntireSheetValues()
    Sheets("Static Data").Range("A1:M100").Value = Sheets("MAIN").Range("A1:M100").Value
End Sub

This works but ideally, i wouldn't define this range and copy all values from one sheet to another

CodePudding user response：

Here's commented code for how I'd accomplish this task via .UsedRange

Sub CopyEntireSheetValues()
    
    'Define and declare workbook and worksheet variables
    Dim wb As Workbook:     Set wb = ThisWorkbook
    Dim wsSrc As Worksheet: Set wsSrc = wb.Worksheets("Main")
    Dim wsDst As Worksheet: Set wsDst = wb.Worksheets("Static Data")
    
    'Set the range you want to copy values from
    Dim rCopy As Range:     Set rCopy = wsSrc.UsedRange
    
    'Bring only the values to the destination sheet
    wsDst.Range("A1").Resize(rCopy.Rows.Count, rCopy.Columns.Count).Value = rCopy.Value
    
End Sub

CodePudding user response：

Two ways. The exact method to do what you want is use the pastespecial method which would be as follows:

Public Sub CopyEntireSheetValues()

    Sheets("MAIN").Range("A1:C100").Copy
    Sheets("Static Data").Range("A1").PasteSpecial (xlPasteValues)
    
   
End Sub

The other is as explained in comment just setting values.

Sheets("Static Data").Range("A1:C100").Value = Sheets("MAIN").Range("A1:C100").Value

In general the setting values is probably the best. The only time I've used the pasteValues is sometimes dates don't quite behave the same when using the second method, but the end result should be the same.

You updated your question, but this could be dynamic (I didn't test).

Public Sub CopyEntireSheetValues()

    Sheets("MAIN").UsedRange.Copy
    Sheets("Static Data").Range(Sheets("MAIN").UsedRange.Address).PasteSpecial (xlPasteValues)
    

End Sub