VBA – Work with Range/Cells and Copy Paste Method

Spread the love

Range – It can be a single cell or combination of multiple cells. by using range certain operation can be done like formatting, manipulation, copy and paste etc.
VBA – Options to select range and its multiple methods

Single cell by Range                               = Range(“a1”).select
Single cell Cells                                      = Cells(1,1).select
Multiple cells by Range                           = Range(“a1:e5”).select
Select all cells on a worksheet                = Cells.select
A Single Row selection                           = Rows(“1:1”).select
Many row’s selection                               = Rows(“1:20”).select
A select a single column                         = Columns(“A:A”).select
Select multiple Columns                          = Columns(“A:E”).select
Different rows (with gaps)                       = Range(“A1,A5,A10”).EntireRow.Select
Different columns (with gaps)                  = Range(“A1,E1,H1”). EntireColumn.Select

 

Copy below codes and try (paste in module) with F8 Key

Sub Ranges_File()

Range(“a1”).Select   ‘ single cell
Cells(1, 1).Select     ‘ single cell
Range(“a1:e5”).Select ‘ multiple cells
Cells.Select               ‘ entire worksheet’s cells
Rows(“1:1”).Select     ‘  single row
Rows(“1:20”).Select   ‘ 1 : 20 rows multiple rows
Columns(“A:A”).Select ‘ single column
Columns(“A:E”).Select  ‘ Multiple columns
Range(“A1,A5,A10”).EntireRow.Select    ‘ multiple different selection for rows
Range(“A1, E1, H1”).EntireColumn.Select ‘ multiple different selection for columns

End Sub

VBA – Copy methods

Copy Method 1 –  describes that how to select sheet, then range and copy to selected cells
Copy Method 2 – describes that select a sheet and define a range to copy
Copy Method 3 -describes that how to select a range within a single statement combination of sheet+range+copy

 

Below are the codes copy and try by F8 key

Sub CopyMethod1()

‘multiple method to copy a range
Sheets(“sheet1”).Select
Range(“a8:c15”).Select
Selection.Copy

End Sub

Sub copyMethod2()

‘copy range
Sheets(“sheet1”).Select
Range(“a8:c15”).Copy

End Sub

Sub CopyMethod3()

‘++++Full reference Sheetname range operation
Sheets(“sheet1”).Range(“a8:c15”).Copy

End Sub

 

VBA – Copy and Paste Special Methods

Copy a range and paste in different location in another worksheet and helps to write codes in a single statement
The paste Special method can vary according to the requirement (find paste special method link).

Copy – below codes and paste it in a module

Sub PasteSpecialMethod()

Sheets(“sheet1”).Range(“a8:c15”).Copy
‘=====copy it Range(“a2”).PasteSpecial Paste and manually add “:=” then pop-up appears, later on add sheet name ==
sheets(“sheet2”).Range(“a2”).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Msgbox “check sheet2 data is pasted”

End Sub

Note: Now check Sheet2 data is pasted over there

Sub PasteSpecialMethod()

Sheets(“sheet1”).Range(“a8:c15”).Copy
‘=====copy it Range(“a2”).PasteSpecial Paste and manually add “:=” then pop-up appears, later on add sheet name ==
sheets(“sheet2”).Range(“a2”).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Msgbox “check sheet2 data is pasted”

End Sub

Note: Now check Sheet2 data is pasted over there

 

add_module