Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
thanks both
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Can't Copy and Paste foumulas? | Excel Discussion (Misc queries) | |||
copy paste | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) | |||
Can't Copy and Paste between Excel 2003 Workbooks | Excel Discussion (Misc queries) |