Вставка значений в миф excel

VBA or Visual Basic for Application is an integration of Microsoft’s VisualBasic  with MS office applications such as MS Excel. VBA is run within the MS Office applications to build customized solutions and programs. These enhance the capabilities of those applications. Today we look at the important Paste Special feature of Excel and VBA. We assume that you have working knowledge of MS Excel and VBA. However, if you are not familiar with the concepts and commands of Microsoft Excel, we recommend that you go through our introductory VBA tutorial .

What is a Macro?

A  Macro is a compact piece of code which is created to customize MS office applications. Macros are created in VBA, which is a subset of Visual Basic and specifically designed to be user friendly. Instead of manually, coding repetitive tasks, it is more efficient to call Macros whenever required. This saves time, effort and money. To learn more about macros, you can take this course on VBA macros.

MS Excel Paste Special Commands

Paste Special is one of the features of Microsoft Office suite. MS Excel permits you to paste only specific aspects of cell data by using the Paste Special Feature. For example, if you need the results of a formula, but not the formula itself, you can choose to paste only the values calculated as the result of the formula. Pastes Special command is used to paste a wide variety of data aspects. Note that the Paste Special option is not applicable to cut data. In order for it to work a cell or range of cells must be copied.

How to use Paste Special in Excel 2010

When you copy a cell or range of cells, and paste it in the destination area you have two methods. They are Paste and Paste Special. Simple Paste does not change anything. However, Paste Special offers a number of options. Here we look at all the options offered in Paste Special Command.

• All–This is similar to the conventional paste.
• Formulas- pastes all the text, numbers and formulas in the selected cell without their formatting.
• Values– This option converts formulas from the copied cell to their calculated values in the destination cell.
• Formats– Here the content is not copied, only the formatting is copied from the source cells to the destination cells.
• Comments– This option pastes only the notes from the source cells to the destination cells.
• Validation– The option pastes only the Data Validation commands into the destination cell range.
• All Using Source Theme- When you select this option the cell styles are also copied along with the other content.
• All Except Borders– The entire content and the formatting without any borders is pasted into the destination cell range.
• Column Widths– Here the column widths of the sources cells are applied to the destination cells.
• Formulas and Number Formats- Includes the number formats of the source cells to the destination cells.
• Values and Number Formats – Here two things happen. Firstly, formulas are converted to their calculated values in the destination cells. Also the number formats of the source are applied to the destination cells.
• All Merging– When you select this option, the conditional formatting of the source cells are applied to the destination cell range.

Mathematical Operations

Paste Special also offers to do some simple mathematical calculations based on the values in source cells and the values in the destination cell range.

• None– This is the default setting. In this no operation is performed.
• Add– The values of the source cells are added to the value(s) in the destination cell.
• Subtract– This option subtracts the values copied from the source cell, from the destination cells values.
• Multiply-This option multiplies the values copied with the values of the destination cells. The result is stored in the destination cell(s).
• Divide- This is similar to multiply option except that division is done instead of multiplication.

Other options of Paste Special:

• Skip Blanks– If this option is selected only the non-empty cells are pasted.
• Transpose– Select this option if you want to change the orientation of the pasted entries.
• Paste Link– Select this option to establish a link between the source and destination cells. Here when the values of the original cells are updated or changed, the values of the destination cells are also correspondingly updated.

To learn about other aspects of Excel 2010 VBA you can take this course.

Excel VBA PasteSpecialMethod

VBA PasteSpecial command pastes a range of cells from the clipboard in to the destination range of cells. The syntax of VBA PasteSpecial looks like this

expression .PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Where expression is a variable that represents a Range object.Let’s take a look at the various parameters of VBA PasteSpecial method. Note that the parameters are all optional.

Example 1

This is a simple example which demonstrates VBA PasteSpecial Method:

With Worksheets("Sheet1")
.Range("A1:A5").Copy
.Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
End With

The values in cells D1:D5 on Sheet 1 is replaced with the sum of the existing content and the contents of cells A1:A5.

To see how this actually works, try out this VBA macro course by Mr Excel.

Example 2: Create a Macro to paste values into a new worksheet

We assume that you know how to open and work on VBA editor. We also assume that you are familiar with Macros in Excel VBA.  If not, we suggest that you read our tutorials on the same.  Here we look a program to create a macro to paste values into a new worksheet.

Sub ExamplePasteSpecial()
Dim ws As Worksheet, wb As Workbook
Set ws = ActiveSheet
Set wb = Workbooks.Add(xlWBATWorksheet)
ws.Range("C5:L19").Copy
wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

In the program, we declare “ws” as variable of type Worksheet and “wb” as variable of type Workbook.  The source cell range is C5: L19. Note that the PasteSpecial function is invoked twice. In the first invocation, only the values calculated by the formula are pasted into the destination range. In the second invocation, only the formatting is copied and not the content.

Example 3: To Copy a Selected Cell and Paste it into another Cell

Sub PasteSpecial1()
Dim Sell_1As Range
Dim NewRowAs Long
Dim RngAs Range
Application.ScreenUpdating = False
NewRow = 401
Set Rng = Range("A1:A400")
For Each Sell_1InRng
Rows(Sell_1.Row & ":" &Sell_1.Row).Select
Selection.Copy
Rows(NewRow& ":" &NewRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
NewRow = NewRow + 1
Next Sell_1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

The PasteSpecial Function copies only the values to the destination cell range. No mathematical operations are performed, no blank cells are omitted and the transpose parameter is set to false. This is a classic example of using PasteSpecial Command in Excel VBA to create a Macro which automates the process.

Example 4:  Excel VBA Macro to Copy, Paste Special Values

Frequently we use formulas to extract data on to a specific worksheet. Then we want to remove the formulas and just keep the data. For that you would copy your selection , right click, chose paste special, select values, click ok and finally hit the enter key to achieve the desired results. Alternatively you could use the programming code below and assign it into a Macro to perform all the six steps mentioned above with a simple mouse click.

Sub CopyPasteSpecVal_1()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

In this program, the formulas are not copied. The destination cell range contains only the calculated values using the formulas. No blanks are skipped. The source cells are not transposed and no mathematical operations are done.

It does not make sense for programmers today to manually enter code and data. It is far better to have Macros to do this especially if the tasks are tedious and repetitive. The PasteSpecial function offers a number of useful options to paste data from the source range to the destination range. Use it wisely and benefit from its power. We hope this tutorial on Excel VBA Paste Special Method was informative. You can always learn more about Excel VBA with this awesome course from Infinite Skills.

Добрый день, товарищи. Помогите, пожалуйста. С вба я дружу не очень хорошо, только начинаю немного изучать. Суть в том, что я написал код для автоматического копирования данных с одного листа на другой . Нужно, чтобы все ячейки вставились значениями, но работает только функция Paste.

Пробовал PasteSpecial Paste:=xlPasteValues, не работает, выдаёт ошибку. А если я просто беру копирую часть ячеек иначе (Range(…).Copy и т.д., тогда значениями вставляется..)

Подскажите, в чём дело. Переписывать по-другому макрос не хочу, потому что практически не умею и на это уйдёт много времени, ибо на этот код убил массу времени

Sub abc()
Workbooks("111.xlsm").Activate
Application.Calculation = xlManual
Worksheets("Лист1").Activate
   Range(Cells(3, 1), Cells(1000000, 21)).ClearContents
Worksheets("выгрузка").Activate
vvv = Worksheets("Лист1").Cells(Worksheets("Лист1").Rows.Count, 1).End(xlUp).Row + 1 
ppp = Worksheets("выгрузка").Cells(Worksheets("выгрузка").Rows.Count, 1).End(xlUp).Row 
        Worksheets("111.xlsm").Range("a2:q" & ppp).Copy
        Worksheets("Лист1").Activate
        Worksheets("Лист1").Range("A" & vvv).Select
        ActiveSheet.Paste
        
Application.Calculation = xlAutomatic

End Sub

В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных. Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.

Обращение к конкретной ячейке

Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

• С помощью Range
• С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

Workbooks("Книга1.xls").Sheets("Лист1").Range("A3") ' Обратиться к ячейке A3
Workbooks("Книга1.xls").Sheets("Лист1").Cells(3, 1) ' Обратиться к ячейке в 3-й строке и 1-й колонке (A3)

Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Range("A1")
Cells(1, 1)

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

With Workbooks("Книга1").Sheets("Лист2")
  ' Вывести значение ячейки A1, которая находится на Листе2
  MsgBox .Range("A1")
  ' Вывести значение ячейки B1, которая находится на Листе2
  MsgBox .Range("B1")
End With

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Application.ActiveCell ' полная запись
ActiveCell ' краткая запись

Чтение значения из ячейки

Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:

• Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
• Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
• Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

MsgBox Cells(1, 1)        ' выведет 01.03.2018
MsgBox Cells(1, 1).Value  ' выведет 01.03.2018
MsgBox Cells(1, 1).Value2 ' выведет 43160
MsgBox Cells(1, 1).Text   ' выведет 01 марта 2018 г.

Dim d As Date
d = Cells(1, 1).Value2    ' числовое представление даты преобразуется в тип Date
MsgBox d                  ' выведет 01.03.2018

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

MsgBox Range("C1")        ' выведет 123,4568
MsgBox Range("C1").Value  ' выведет 123,4568
MsgBox Range("C1").Value2 ' выведет 123,456789
MsgBox Range("C1").Text   ' выведет 123,457р.

Dim c As Currency
c = Range("C1").Value2    ' значение преобразуется в тип Currency
MsgBox c                  ' выведет 123,4568

Dim d As Double
d = Range("C1").Value2    ' значение преобразуется в тип Double
MsgBox d                  ' выведет 123,456789

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Dim s As String
Dim i As Integer
s = Range("B1").Value2 ' успех
i = Range("B1").Value2 ' ошибка

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Range("A1") = 123.45
Range("A1").Value = 123.45
Range("A1").Value2 = 123.45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

Cells(2, 1) = #3/1/2018#
Cells(2, 1).Value = #3/1/2018#
Cells(2, 1).Value2 = #3/1/2018#

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.