copy and paste with offset

Hi
I have a worksheet that contains rows of data i filter the blank rows
out leaving valid rows of data
what i want to do is copy the rows and paste them onto another
worksheet in the first blank row blank i know i need to use the offset
but is it posable to select a range(A2:AB25)
then paste this range onto the other worksheet

thanks



kevin

Hi Kevin,

Try something like:

'=============>>
Sub Tester()
Dim WB As Workbook
Dim srcSH As Worksheet
Dim destSH As Worksheet
Dim srcRng As Range
Dim destRng As Range

Set WB = ActiveWorkbook '<<====== CHANGE
Set srcSH = WB.Sheets("Sheet1") '<<====== CHANGE
Set destSH = WB.Sheets("Sheet2") '<<====== CHANGE

Set destRng = destSH.Cells(Rows.Count, "A").End(xlUp)(2)

On Error Resume Next
Set srcRng = srcSH.Range("A2:AB25") '<<====== CHANGE
On Error GoTo 0

If Not srcRng Is Nothing Then
srcRng.Copy Destination:=destRng
End If

End Sub
'<<=============



---
Regards,
Norman


"kevcar40" <[email protected]> wrote in message
news:[email protected]...
> Hi
> I have a worksheet that contains rows of data i filter the blank rows
> out leaving valid rows of data
> what i want to do is copy the rows and paste them onto another
> worksheet in the first blank row blank i know i need to use the offset
> but is it posable to select a range(A2:AB25)
> then paste this range onto the other worksheet
>
> thanks
>
>
>
> kevin
>

Hi Kevin.

> Set srcRng = srcSH.Range("A2:AB25") '<<====== CHANGE

was intended to be:

Set srcRng = srcSH.Range("A2:AB25").SpecialCells(xlVisible)

---
Regards,
Norman


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Kevin,
>
> Try something like:
>
> '=============>>
> Sub Tester()
> Dim WB As Workbook
> Dim srcSH As Worksheet
> Dim destSH As Worksheet
> Dim srcRng As Range
> Dim destRng As Range
>
> Set WB = ActiveWorkbook '<<====== CHANGE
> Set srcSH = WB.Sheets("Sheet1") '<<====== CHANGE
> Set destSH = WB.Sheets("Sheet2") '<<====== CHANGE
>
> Set destRng = destSH.Cells(Rows.Count, "A").End(xlUp)(2)
>
> On Error Resume Next
> Set srcRng = srcSH.Range("A2:AB25") '<<====== CHANGE
> On Error GoTo 0
>
> If Not srcRng Is Nothing Then
> srcRng.Copy Destination:=destRng
> End If
>
> End Sub
> '<<=============
>
>
>
> ---
> Regards,
> Norman

thanks both