Paste Range to New Workbook
 

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

Paste Range to New Workbook
 

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



"nejer2000" wrote:

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

Paste Range to New Workbook
 

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

Paste Range to New Workbook
 

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

Paste Range to New Workbook
 

Hi Chad,

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

Cheers,

Jenna


ChadF wrote:

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



"nejer2000" wrote:

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

Paste Range to New Workbook
 

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


Incidental wrote:

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