Activecell range copy

[Allan]

So heres what I'm Try to do.

Copy a range1 from sheet1 to sheet2 to first blank row Col "A".
The range1 from sheet1 is of variable size and may contain empty cells.

Also range1 may not be in the same place everytime on sheet1, but
I have the current cell address which is the first column and last row of
the range.

curcell = ActiveWindow.RangeSelection.Address

Maybe using .Resize to determine range size??

Thanks Allan

 

[SeanC UK]

Hi Allan,

You can use things like:

Cells(ActiveCell.Row, ActiveCell.Column).End(xlUp).Select

to move up the column, but this will only take you from the current cell up
to the first cell below a blank cell. So if your range does contain a blank
above the curcell then you will need to use the above code again to jump the
blanks to the next non-empty cell, and again to move to the top of the next
block, and so on. The same methods can be used to move right across your
range, but once again you will run into problems using this method when
blanks exist. If you know more about your range then you might be able to do
it, for example, if you know that the range always starts on row 1, or if you
know there will be no blank cells in the first row of data, or if you know
that the number of columns will always be consistent.

If you don't mind having user input then you can ask for the range to be
selected manually using something like:

Dim myRange As Range
On Error Resume Next
Set myRange = Application.InputBox("Select Range", "Range", Type:=8)
If Err.Number <> 0 Then
'INPUT BOX WAS CANCELLED
On Error GoTo 0
Else
'RANGE SELECTED
On Error GoTo 0
myRange.Select
End If

I hope this helps,

Sean.

 

[Bob Phillips]

maybe?

With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Activecell.CurrentRegion.Copy .Cells(LastRow, "A")
End With

--
---
HTH

Bob

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

 

[Allan]

After putzing around for a while this is what I come up with.
This works because the active cell is in the CurrentRegion.

Dim curcell As Variant
Dim lastrow As Long
lastrow = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row
If lastrow <> 1 Then lastrow = lastrow + 1

curcell = ActiveCell.Address ' anywhere in the current region

Selection.Resize(Selection.Rows.Count, Selection.Columns.Count).Select
Range(curcell).CurrentRegion.Copy Sheets("sheet2").Cells(lastrow, 1)

End Sub