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" wrote in message
ups.com...
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" wrote in message
...
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