VBA Range Copy

lpsd

New Member
Joined
Sep 22, 2005
Messages
35
Hello,

I need to do the following in VBA. Copy a column of cells (variable length) to the right X number of cells. For example:

copy contents J12:J19 to J12:L19 or to L12:P19

the top cell of the destination is always at the same row of the source cells.

thanks in advance,
lowell
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
How do you determine the start column and the end column of the destination range?
 

lpsd

New Member
Joined
Sep 22, 2005
Messages
35
hi,
i forgot to tell the board that i have stored the start column of the destination in a variable called STCOL and i have stored the numbers of columns to copy in a variable called NOCOL.

for example:

the source J12:J19 will be copied to the destination starting in J12 (column 10 or STCOL = 10) and it will be copied for 3 col or NOCOL = 3. the the source will be duplicated from J12:L12

sorry for the confusion.
lowell
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

get vb Help file and refer "Resize"

e.g
Code:
Sub test()
MsgBox "j12:j19.Resize(,3).Address = " & vbLf & _
            Range("j12:j19").Resize(,3).Address
End Sub
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328

ADVERTISEMENT

Code:
Dim sourceRange As Range, STCOL%, NOCOL%
Set sourceRange = [J12:J19]
STCOL = 10
NOCOL = 3
sourceRange.Copy sourceRange.Offset(0, STCOL - sourceRange.Column).Resize(, NOCOL)
 

lpsd

New Member
Joined
Sep 22, 2005
Messages
35
boller, jindon: thanks for your advice. the code you supplied works. question:

after selecting the range to copy i want to name it SOURCERANGE so it can be used in the code below. but each time i use currentregion.select i get all adjacent cells. i just want the selected (one column) to be named.

can you help?
lowell

Range(Selection, Selection.End(xlDown)).Select

' NAME THE SELECTED RANGE HERE. CALL IT SOURCERANGE'

sourceRange.Copy sourceRange.Offset(0, STCOL - sourceRange.Column).Resize(, NOCOL)
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Select only the first cell of the required source range, then :-

Set sourceRange=Range(Selection, Selection.End(xlDown))
 

lpsd

New Member
Joined
Sep 22, 2005
Messages
35
boller,
i appreciate you help. i've learned a lot from you which i will now apply

lowell
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,726
Members
410,702
Latest member
clizama18
Top