Copying a variable range

B

bobclark

OK, here is what I want to do
sheet1
copy sheet 2 range starting point.offset (0,down by the contents of a cell
value)
back to sheet 1 (edit|special|value)

Thanks in advance for all of the answers I have been given, I tried to look this
up in dejanews (now google) but no luck
 
D

Dave Peterson

Lots of blanks to complete in my suggestion:

Option Explicit
Sub testme1()

Dim myRng As Range
Dim myStartCell As Range
Dim myDownCell As Range
Dim DestCell As Range

Set myDownCell = Worksheets("sheet1").Range("a1")
Set myStartCell = Worksheets("sheet2").Range("c9")
Set myRng = myStartCell.Resize(myDownCell.Value)
Set DestCell = Worksheets("sheet1").Range("x7")

myRng.Copy
DestCell.PasteSpecial Paste:=xlPasteValues

'or just assign the values
DestCell.Resize(myDownCell.Value).Value _
= myStartCell.Resize(myDownCell.Value).Value

End Sub

Where's the cell that contains the value to resize? Where to paste? Where to
start?

I think I'd check to see if that mydowncell.value was valid, too.
 
B

bobclark

Thanks again, can I adopt you :)
HTH
Bob

~>Lots of blanks to complete in my suggestion:
~>
~>Option Explicit
~>Sub testme1()
~>
~> Dim myRng As Range
~> Dim myStartCell As Range
~> Dim myDownCell As Range
~> Dim DestCell As Range
~>
~> Set myDownCell = Worksheets("sheet1").Range("a1")
~> Set myStartCell = Worksheets("sheet2").Range("c9")
~> Set myRng = myStartCell.Resize(myDownCell.Value)
~> Set DestCell = Worksheets("sheet1").Range("x7")
~>
~> myRng.Copy
~> DestCell.PasteSpecial Paste:=xlPasteValues
~>
~> 'or just assign the values
~> DestCell.Resize(myDownCell.Value).Value _
~> = myStartCell.Resize(myDownCell.Value).Value
~>
~>End Sub
~>
~>Where's the cell that contains the value to resize? Where to paste? Where to
~>start?
~>
~>I think I'd check to see if that mydowncell.value was valid, too.
~>
~>
~>Bob, Clark wrote:
~>>
~>> OK, here is what I want to do
~>> sheet1
~>> copy sheet 2 range starting point.offset (0,down by the contents of acell
~>> value)
~>> back to sheet 1 (edit|special|value)
~>>
~>> Thanks in advance for all of the answers I have been given, I tried to look
this
~>> up in dejanews (now google) but no luck
 
B

bobclark

~>Lots of blanks to complete in my suggestion:
~>
~>Option Explicit
~>Sub testme1()
~>
~> Dim myRng As Range
~> Dim myStartCell As Range
~> Dim myDownCell As Range
~> Dim DestCell As Range
~>
~> Set myDownCell = Worksheets("sheet1").Range("a1")
~> Set myStartCell = Worksheets("sheet2").Range("c9")
~> Set myRng = myStartCell.Resize(myDownCell.Value)
~> Set DestCell = Worksheets("sheet1").Range("x7")
~>
~> myRng.Copy
~> DestCell.PasteSpecial Paste:=xlPasteValues
~>
~> 'or just assign the values
~> DestCell.Resize(myDownCell.Value).Value _
~> = myStartCell.Resize(myDownCell.Value).Value
~>
~>End Sub
~>
~>Where's the cell that contains the value to resize? Where to paste? Where to
~>start?

it varies each time, so I will hopefully start in the activecell (which will be
blank) for the receiving cell and start the macro there filling in the
remaining blank rows which are counted by the offset counting cell. basically I
have a series of between 32 and 100 rows of data, and between 20 and 60 of the
cells need to be replaced daily/weekly, depending on the user, to allow for
rapidly changing variables located on a master data sheet. when it is finished I
want to have the top 40-60 inactive cells replaced with the top 40-60 available
options so that the options available to the end user are the "cream of the
crop". Your answer for the deletion of the cells to be replaced works, soI am
that far in the macro. by hand it takes about 4 hours of work, completely
unacceptable and unnecessary.

~>
~>I think I'd check to see if that mydowncell.value was valid, too.

That has been verified, I've written everything to be able to accomplish the cut
and paste exactly as needed.

There are 24 different paste to locations within the spreadsheets (4 on 6
different sheets). The paste from locations, although there are 24 on each
sheet, are standardized within all 7 workbooks.

~>
~>
~>Bob, Clark wrote:
~>>
~>> OK, here is what I want to do
~>> sheet1
~>> copy sheet 2 range starting point.offset (0,down by the contents of acell
~>> value)
~>> back to sheet 1 (edit|special|value)
~>>
~>> Thanks in advance for all of the answers I have been given, I tried to look
this
~>> up in dejanews (now google) but no luck
 
D

Dave Peterson

I'm usually hesitant to start with the activecell.

If I'm running the macro (and got enough sleep the night before), it might work
ok.

But if it's possible, I try to either ask:

dim startcell as range
set startcell = nothing
on error resume next
set startcell = application.inputbox("where to start",type:=8).cells(1)
on error goto 0

if startcell is nothing then
'user cancelled
exit sub
end if

'do the work here.

===
If you can rely on something on the worksheet, maybe even a .find() and offset
from there???

========
I'm packing my bags. Please have my room made up.

I guess I won't have to care about that pesky 401k and the stock market anymore.

See you soon, Dad!

Retirement awaits!
 
B

bobclark

aw gee Son, we'll give you the basement, separate entrance, but no loud parties,
and no sleep-overs. plus you get an earthlink dial up, using parental controls
of course (no way you're clogging my broadband)

Which flight will you be on :)

On Wed, 13 Oct 2004 18:22:08 -0500, in microsoft.public.excel.misc falling into
the bathtub with your monitor, the short circuit caused the following to
mysteriously appear from your keyboard:

~>I'm usually hesitant to start with the activecell.
~>
~>If I'm running the macro (and got enough sleep the night before), it might
work
~>ok.
~>
~>But if it's possible, I try to either ask:
~>
~>dim startcell as range
~>set startcell = nothing
~>on error resume next
~>set startcell = application.inputbox("where to start",type:=8).cells(1)
~>on error goto 0
~>
~>if startcell is nothing then
~> 'user cancelled
~> exit sub
~>end if
~>
~>'do the work here.
~>
~>===
~>If you can rely on something on the worksheet, maybe even a .find() andoffset
~>from there???
~>
~>========
~>I'm packing my bags. Please have my room made up.
~>
~>I guess I won't have to care about that pesky 401k and the stock market
anymore.
~>
~>See you soon, Dad!
~>
~>Retirement awaits!
~>
~>> ~>Where's the cell that contains the value to resize? Where to paste?
Where to
~>> ~>start?
~>>
~>> it varies each time, so I will hopefully start in the activecell (which will
be
~>> blank) for the receiving cell and start the macro there filling in the
~>> remaining blank rows which are counted by the offset counting cell.
basically I
~>> have a series of between 32 and 100 rows of data, and between 20 and 60 of
the
~>> cells need to be replaced daily/weekly, depending on the user, to allow for
~>> rapidly changing variables located on a master data sheet. when it is
finished I
~>> want to have the top 40-60 inactive cells replaced with the top 40-60
available
~>> options so that the options available to the end user are the "cream of the
~>> crop". Your answer for the deletion of the cells to be replaced works, so I
am
~>> that far in the macro. by hand it takes about 4 hours of work, completely
~>> unacceptable and unnecessary.
~>>
 
D

Dave Peterson

Whatever flight, I'll want first class seats!

Ok. Let's get serious. Let's talk allowance. <vbg>

aw gee Son, we'll give you the basement, separate entrance, but no loud parties,
and no sleep-overs. plus you get an earthlink dial up, using parental controls
of course (no way you're clogging my broadband)

Which flight will you be on :)
Click to expand...
<<snipped>>
 
Top