VBA Range Copy

[lpsd]

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

 

[Boller]

How do you determine the start column and the end column of the destination range?

 

[lpsd]

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]

Hi

get vb Help file and refer "Resize"

e.g

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

 

[Boller]

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]

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]

Select only the first cell of the required source range, then :-

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

 

[lpsd]

boller,
i appreciate you help. i've learned a lot from you which i will now apply

lowell