How could the following code be adjusted so that it copies the selected row from columns F to I and paste them in from column C to F of the OrderData Sheet with the cell content of O6 being copied to the column B of the row where the rest of the data gets copied.
Sub copyrow()
Dim myrow As Integer
myrow = ActiveWindow.RangeSelection.Row
Rows(myrow).Select 'This copies the selected row
Selection.Copy
ActiveSheet.Paste Destination:=Worksheets("OrderData").Cells(3, 1)
Application.CutCopyMode = False
End Sub
Sub CopyRow2()
Dim myrow As Long
myrow = ActiveWindow.RangeSelection.Row
ActiveSheet.Range("F" & myrow & ":I" & myrow).Copy Destination:=Worksheets("OrderData").Range("C3")
ActiveSheet.Range("O6").Copy Destination:=Worksheets("OrderData").Range("B3")
Application.CutCopyMode = False
End Sub
Thanks for the help Hans. My column Headers in Sheet "OrderData" starts from row 4 onwards.
How could the code be made so that it would copy the selected rows from the active sheet and keep on pasting them from row 5 onwards.
Lets say for example if the value on O6 is 0002 and the column B of the sheet has a row with 0002, the code would paste the copied row below the row containg 0002.
Sub CopyRow2()
Dim myrow As Long
Dim wsh As Worksheet
Dim rng As Range
Dim r As Long
myrow = ActiveWindow.RangeSelection.Row
Set wsh = Worksheets("OrderData")
Set rng = wsh.Range("B:B").Find(What:=Range("O6").Value, LookIn:=xlValues, LookAt:=xlWhole)
If rng Is Nothing Then
r = wsh.Range("B" & wsh.Rows.Count).End(xlUp).Row + 1
Else
rng.Offset(1, 0).EntireRow.Insert
r = rng.Row + 1
End If
ActiveSheet.Range("F" & myrow & ":I" & myrow).Copy Destination:=Worksheets("OrderData").Range("C" & r)
ActiveSheet.Range("O6").Copy Destination:=Worksheets("OrderData").Range("B" & r)
Application.CutCopyMode = False
End Sub
Last edited by HansV on 08 Feb 2011, 16:51, edited 1 time in total.
Reason:to correct error
The code now copies the row. But it does not insert a row above the row that contains another number such as other than 0002.
Instead it copies the row to the first empty row below the last data row?
I've inserted the following line to the code at Post 42131 just above the End Sud, so that the row gets copied to the appropriate row of the "OrderData" sheet.
My question of concern is, Instead of having the user to select the row to be copied; how could the code be made so that it copies the rows within the range F18: O37 that is being highlighted with Interior.ColorIndex = 6.