Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting the absolute range address from a dynamic named range | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
copying dynamic range based on cell outside of range | Excel Programming | |||
select dynamic range with dynamic start point | Excel Programming | |||
Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming |