Dynamic range with VBA
 

I need to copy a variable sized range of data from one sheet and paste it
onto the end of a list on another sheet from a VBA routine. I have tried
accessing the variable source range via a dynamic named range setup in the
worksheet itself but my limited skill grinds to a halt when I try to grab
that data and append it to the new list. Can someone help me with a bit of
code for this?
Thanks in advance,
RDW

Dynamic range with VBA
 

With Worksheets("Sheet2")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
Worksheets("Sheet1").Range("myRange").Copy .Cells(iLastRow+1,"A")
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"RD Wirr" wrote in message
...
I need to copy a variable sized range of data from one sheet and paste it
onto the end of a list on another sheet from a VBA routine. I have tried
accessing the variable source range via a dynamic named range setup in the
worksheet itself but my limited skill grinds to a halt when I try to grab
that data and append it to the new list. Can someone help me with a bit of
code for this?
Thanks in advance,
RDW

Dynamic range with VBA
 

You can use the statement
datarange.show
dataRangeString = dataRange.RefEditPlotCell.Value

This will create a pop up window where you can highlight the cells you want
to copy and return a sting with the range of cells.

"RD Wirr" wrote:

I need to copy a variable sized range of data from one sheet and paste it
onto the end of a list on another sheet from a VBA routine. I have tried
accessing the variable source range via a dynamic named range setup in the
worksheet itself but my limited skill grinds to a halt when I try to grab
that data and append it to the new list. Can someone help me with a bit of
code for this?
Thanks in advance,
RDW

Dynamic range with VBA
 

Thanks for the tip Joel, Cool how that works. But for my application, I need
it to just get all the data within a dynamic range and append it to a list on
another sheet....all in the background. Formulas within the source range
already calculate what data needs to be transferred. Bob's routine above
works great. Now I just need to get it to copy over only the values rather
than the fomulas.

Thanks,
RDW

"Joel" wrote:

You can use the statement
datarange.show
dataRangeString = dataRange.RefEditPlotCell.Value

This will create a pop up window where you can highlight the cells you want
to copy and return a sting with the range of cells.

"RD Wirr" wrote:

I need to copy a variable sized range of data from one sheet and paste it
onto the end of a list on another sheet from a VBA routine. I have tried
accessing the variable source range via a dynamic named range setup in the
worksheet itself but my limited skill grinds to a halt when I try to grab
that data and append it to the new list. Can someone help me with a bit of
code for this?
Thanks in advance,
RDW

Dynamic range with VBA
 

Hi Bob,

Thanks very much for the help. That copies the data over perfectly. Just one
thing I forgot to mention. The source data has some formulas that change each
time which then also changes the data copied to the list. I need to copy over
the current values and formats but not formulas. Can you tell me how I do
this?

Thanks again,
RDW

"Bob Phillips" wrote:


With Worksheets("Sheet2")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
Worksheets("Sheet1").Range("myRange").Copy .Cells(iLastRow+1,"A")
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"RD Wirr" wrote in message
...
I need to copy a variable sized range of data from one sheet and paste it
onto the end of a list on another sheet from a VBA routine. I have tried
accessing the variable source range via a dynamic named range setup in the
worksheet itself but my limited skill grinds to a halt when I try to grab
that data and append it to the new list. Can someone help me with a bit of
code for this?
Thanks in advance,
RDW