Paste Range to New Workbook

[nejer2000]

Hi Folks,

Still a beginner and winging my way through.... Trying to write some
code to workthrough a column, select the row according to some
condition and copy it to a new workbook (with the header row -
Range("1:1"). This should be a relatively simple task however, for
some reason when I get to the Selection.Paste command I get the error:
Run-time error '438': Object doesn't support this property or method.
I've tried a number of different options to no avail. Does anyone have
any suggestions as to how I might resolve this issue? Your help would
be most appreciated. Current code as follows:


Sub PasteRowToNewWB()
Dim r As Integer
Dim CellVal As String

Sheets("Actions").Select
For r = 2 To LastRowWithData() 'Function returns number of last
row with data.
CellVal = Cells(r, 9).Text

If CellVal = "Overdue" Then
Range("1:1", r & ":" & r).Select
Range("A3").Activate
Selection.Copy
Workbooks.Add
ActiveWorkbook.Sheets("Sheet1").Activate
'MsgBox "The active worksheet is: " & Worksheet.Name
Selection.Paste

End If
Next r
End Sub

 

[Guest]

Hi,

You might want to consider the following...

Public Sub CopyToNewWorkbook
Dim aWB as Workbook
Dim aWS as Worksheet
Dim TargetRange as Range
Dim CopyFrom as Range
Dim R as Integer
Dim CellVal as String

' you dont need to necessarily Activate / Select
' workbooks / sheets to copy values to / from them ...

Workbooks.Add
Set aWB = ActiveWorkbook
Set aWS = aWB.Sheets(1)

' Your code
For R = 2 to LastRowWithData()
CellVal = Cells(r, 9).Text
If Trim(CellVal) = "Overdue" then
Set CopyFrom = Range("1:1", R &":"&R)
Set TargetRange = aWS.Range("1:1", R &":" &R)

TargetRange.Value = CopyFrom.Value
End If
Next R

End Sub

Chad

 

[Incidental]

Hi there

Ran a quick test with your code, think it may work if you use
"Selection.PasteSpecial"

if you just want the values use

Selection.PasteSpecial xlValues

hope it helps

 

[Incidental]

Hi there

Ran a quick test with your code, think it may work if you use
"Selection.PasteSpecial"

if you just want the values use

Selection.PasteSpecial xlValues

hope it helps

 

[nejer2000]

Hi Chad,

Such pretty code. Thank you kindly, this looks promising...

Cheers,

Jenna

 

[nejer2000]

Works a charm - and when I think of the hours I've wasted looking for
this! Much appreciated.