Scribd
Upload
26 views1 page

VBA Copy and Paste Techniques

This document provides examples of copying and pasting data in VBA macros using different methods: - Simple copy and paste transfers all data including formatting between cells, ranges, and worksheets. - PasteSpecial allows copying just formatting or values to avoid transferring formulas. This is used with Copy followed by PasteSpecial on the destination cell or range. - Ranges can be copied and pasted on the same sheet, between sheets in the same workbook, or between different workbooks specified by name. CurrentRegion property copies a range and its dependent formulas.

Uploaded by

jhon
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views1 page

VBA Copy and Paste Techniques

This document provides examples of copying and pasting data in VBA macros using different methods: - Simple copy and paste transfers all data including formatting between cells, ranges, and worksheets. - PasteSpecial allows copying just formatting or values to avoid transferring formulas. This is used with Copy followed by PasteSpecial on the destination cell or range. - Ranges can be copied and pasted on the same sheet, between sheets in the same workbook, or between different workbooks specified by name. CurrentRegion property copies a range and its dependent formulas.

Uploaded by

jhon
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SAMPLE SCRIPT COPY PASTE– INDOTRAININGCENTER.

COM

Private Sub TestingThis()


'Paste the snippets of code below in here to test them
End Sub
Private Sub CopyingAndPasting()
'Note: All the "Simple" Copy/Paste get everything (formats included)
'See the PasteSpecial code a little further down to avoid this
'************ SIMPLE COPY/PASTE ************
'Simple Copy and pasting on the same sheet
'Source range Destination Range
Range("A1").Copy Range("B1")
'Source range Destination Range
Range("A1:A3").Copy Range("B1:B3")
'Alternative method
Range("A1:A3").Copy Range("B1")
'Simple Copy and pasting on another sheet (same workbook)
'Source sheet, and range Destination sheet & range
Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")
'Simple Copy and pasting on another sheet (same workbook)
'Specify Workbook name, Sheet name, and Range on BOTH sides of the "Copy"
Workbooks("[Link]").Worksheets("Sheet1").Range("A1").Copy _
Workbooks("My Macro [Link]").Worksheets("Sheet2").Range("B1")
'As above, but using the "Current Region" property
Workbooks("[Link]").Worksheets("Sheet1").Range("A1").[Link]
_
Workbooks("My Macro [Link]").Worksheets("Sheet2").Range("B1")
'************ COPY/PASTE (SPECIAL) ************
'NOTE: While single line copy/pastes do NOT produce marching ants
'Seperate line copy/pastes do. Remove the marching ants with this:
[Link] = False
'Copy/Paste the FORMATTING with PasteSpecial
'Source
Range("A1").Copy
'Destination paste only the formatting
Range("B1").PasteSpecial xlPasteFormats
'Remove marching ants
[Link] = False
'Copy/Paste the VALUE with PasteSpecial
'Source
Range("A1").Copy
'Destination paste only the value (not the formula)
Range("B1").PasteSpecial xlPasteValues
'Remove marching ants
[Link] = False

End Sub

You might also like