Need to copy row from input box answer, but unable to do so. Facing difficulties!

excelnewbie17 · Jul 25th 2017

Dear all,

See code below. What I am trying to do is to use an input box for the user to type in the Row in which to copy the entire row from. E.g. if he chooses 3, "ChooseRow" will be 3 and from there I want to be able to copy selected data from row 3.

Error happens in red below. Unable to choose row 3, if user inputted 3 as his desired row. Any help greatly appreciated. Thanks!

Code

Dim RowCount As Integer
Dim ChooseRow As Integer




xpproduction.Activate
RowCount = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1


If RowCount > 1 Then
ChooseRow = InputBox("please select the row you want")


Set copyRange = ActiveSheet.Range("A2:A" & ChooseRow + 1) 'ERROR LINE
copyRange.SpecialCells(xlCellTypeVisible).Copy

Display More

NOTE: Please use code tags around your code, I have added them for you this time.

skywriter · Jul 25th 2017

Re: Need to copy row from input box answer, but unable to do so. Facing difficulties!

It might be helpful if you upload a sample file. :cheers:

bob22 · Jul 25th 2017

Re: Need to copy row from input box answer, but unable to do so. Facing difficulties!

I did a bit of revising; not sure what you are trying to do.
This code looks at Column A and selects a range using your Input box and then pastes it in row 12.

Code

Sub test()
Dim RowCount, ChooseRow As Integer
Dim xpproduction As Sheets
Dim copyRange As Range
 
Worksheets("xpproduction").Activate
RowCount = Cells(Rows.Count, "A").End(xlUp).Row
 
If RowCount > 1 Then
    ChooseRow = InputBox("please select the row you want")
     
    Set copyRange = ActiveSheet.Range("A2:A" & ChooseRow + 1) 'ERROR LINE
    copyRange.SpecialCells(xlCellTypeVisible).Copy
    
    With Sheets("xpproduction").Cells(12, 1)
       .PasteSpecial xlPasteValues
    End With
    
End If


End Sub

Display More

Need to copy row from input box answer, but unable to do so. Facing difficulties!

Participate now!

Share