Копирование только значений vba excel

I assume you want to copy from «Sheet1» to «Sheet1» — of course you can make this a parameter and loop through all your sheets

Dim rSource as range 'Source Range
Dim rDest as range   'Target Range - It should be the same dimension
Dim wbSource as workbook 'Source Workbook
Dim wbTarget as workbook 'Target Workbook
Dim myRange as string

myRange = "A:G" ' It is an example, you can make it dynamic

'add new workbook
    Workbooks.Add
    Set wbTarget = ActiveWorkbook

'Set the Source Range
    Set rSource = wbSource.Sheets("Sheet1").Range(myRange)

'Destination Range
    Set rDest = wbTarget.Sheets("Sheet1").Range(myRange)

'Copy values only
    rSource.Copy
    rDest.PasteSpecial xlPasteValues

answered Oct 8, 2018 at 17:20

You need to use the pastespecial command as shown below.

'Source range
Set r = Sheets("Sheet1").Range("Table1")

'Destination range
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))

r.Copy 
dest.pastespecial paste:=xlPasteValues

answered Jun 18, 2014 at 21:15

I achieve a solution that works.

There follows the code:

    Set r = Sheets("Criteria").Range("CriteriaTable")

    Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))
    Sheets("Criteria").Activate
    r.Select
    Selection.Copy
    Sheets("Load").Activate
    dest.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

answered Jun 20, 2014 at 15:28

Use the Range.Value method. Its like setting a variable a = 1. Where you think of it as copying 1 to a. So…

Range2.value = Range1.value

or

Worksheets("historical").Range("A1:F15").Value =  Worksheets("actuals").Range("A1:F15").Value

Here I’m copying some data in worksheet actual to some historical worksheet. Or if you prefer setting the value of one range to another range.

You can skip the copy command altogether as MP24 said .. his suggestion worked for me after modifyibg the last line from «value» to «formula» as follows

Set r = Sheets(«Sheet1»).Range(«Table1»)
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual +
r.Rows.Count — 1, 5))

dest.formula = r.formula

answered Aug 1, 2022 at 6:42

Sub m()
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Общий" Then
           myR_Total = Sheets("Общий").Range("A" & Sheets("Общий").Rows.Count).End(xlUp).Row
           myR_i = Sheets(i).Range("A" & Sheets(i).Rows.Count).End(xlUp).Row

           If i = 1 Then
            Sheets(i).Rows("1:" & myR_i).Copy Sheets("Общий").Range("A" & myR_Total)
           Else
            Sheets(i).Rows("2:" & myR_i).Copy Sheets("Общий").Range("A" & myR_Total)
           End If

        End If
    Next
End Sub

Помогите пожалуйста откорректировать код макроса.
Он работает, только копирует в лист «Общий» формулы из всех других листов.
А мне нужны значения.

Ответ дали здесь

Sub m()
Set Sh = Worksheets("Общий")
For i = 1 To Sheets.Count
    myR_Total = Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row
    With Sheets(i)
        If .Name <> "Общий" Then
            myR_i = .Range("A" & .Rows.Count).End(xlUp).Row
            j = IIf(i = 1, 1, 2)
            .Rows(j & ":" & myR_i).Copy
            Sh.Range("A" & myR_Total).PasteSpecial Paste:=xlPasteValues
        End If
    End With
Next
End Sub

16 апр. 2023, в 09:40

10000 руб./за проект

16 апр. 2023, в 08:25

20000 руб./за проект

16 апр. 2023, в 06:36

1000 руб./за проект