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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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
 

Forum statistics

Threads
1,140,912
Messages
5,703,128
Members
421,276
Latest member
davidfrommke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top