i would like to know how do i am using paste specail in excel VBA

i would like to copy from one spreadsheet to another and i would like to know what is the command for copying value and for coping formula

for example i have the following line, how its should be written for each case (for value and for formula)?

wksDest.Range(Cells(rng.Row, 244), Cells(rng.Row, 318)).Copy
               wksSource.Activate
               Cells(lastRow + 1, 107).PasteSpecial xlPasteAll, Paste:=xlPasteCommens

and what is the meaning of .PasteSpecial xlPasteAll

Thanks
Gvilbis
gvilbisAsked:
Who is Participating?
 
RobOwner (Aidellio)Commented:
You can also find out about what's required or default by using the inspector (F2) in the VBA editor.  As you can see below, xlPasteAll is the default as defined by the function

vba editor / inspector
0
 
MacroShadowCommented:
Sub Demo()
    ' to copy
    wksDest.Range(Cells(Rng.Row, 244), Cells(Rng.Row, 318)).Copy
    ' to paste values
    wksSource.Range(Cells(lastRow + 1, 107)).PasteSpecial xlPasteAll, Paste:=xlPasteValues
    ' to paste formulas
    wksSource.Range(Cells(lastRow + 1, 107)).PasteSpecial xlPasteAll, Paste:=xlPasteFormulas
End Sub

Open in new window


Paste is the argument which determines what part of the range will be copied. The options are:
xlPasteAll - Everything will be pasted.
xlPasteAllExceptBorders - Everything except borders will be pasted.
xlPasteAllMergingConditionalFormats - Everything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceTheme - Everything will be pasted using the source theme.
xlPasteColumnWidths - Copied column width is pasted.
xlPasteComments - Comments are pasted.
xlPasteFormats - Copied source format is pasted.
xlPasteFormulas - Formulas are pasted.
xlPasteFormulasAndNumberFormats - Formulas and Number formats are pasted.
xlPasteValidation - Validations are pasted.
xlPasteValues - Values are pasted.
xlPasteValuesAndNumberFormats - Values and Number formats are pasted.
0
 
gvilbisAuthor Commented:
what is the default of pasting if not mention Paste:=xlPasteFormulasvalues or Paste:=xlPasteValues?

Thanks
Gvilbis
0
 
RobOwner (Aidellio)Commented:
As MacroShadow has indicated xlPasteAll will copy all formatting, formulas and values from the range you have selected to copy.

It may give you a better idea about what's happening by just performing a paste special via the ribbon

ie

paste special options
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.