Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a range of cells and paste into a new workbook in differentcolumns | Excel Discussion (Misc queries) | |||
Paste Filtered Range to New Workbook- AS | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) | |||
Need a macro to copy a range in one workbook and paste into another workbook | Excel Programming | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming |