copy rows to another file

C

climate

Dear experts
I have following code which Mike has wroted, i need to change this macro,
this code is able to copy several rows (based on values on CoLG) to sheet 2.
i want to copy rows to new file (r.xls) on sheet1, in other hand, when i run
it on any file, copy rows to sheet1 of r.xls and set rows bottom to bottom.

Would you please guide me?
regards

Sub Marine()
Dim arrParts() As String
Dim MyRange As Range, CopyRange As Range
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = Range("G1:G" & LastRow)
response = InputBox("Enter rows to copy in the format nnn,nnn,nn")
arrParts = Split(response, ",")
For Each C In MyRange
For Each strPart In arrParts
If C.Value = Val(strPart) Then
If CopyRange Is Nothing Then
Set CopyRange = C.EntireRow
Else
Set CopyRange = Union(CopyRange, C.EntireRow)
End If
End If
Next
Next

If Not CopyRange Is Nothing Then
LastRow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
CopyRange.Copy Sheets("Sheet2").Range("A" & LastRow + 1)
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top