Search code examples
excelvbacopyrowcut

Copy - paste cell value from multiply rows

I have a question. I have multiply rows. and few values in column by each row.

I need macro that let me to choose any row to take values from column to another column.

enter image description here

For example. I have rows from 5 to 23. And i have values in each row by column H, K, N, Q, T. one time i need to cut all values from row 6 and past in to columns W, Z..., Other time i need to do that with row 15.

If there are some macros that can allowed me to write down row number and run macros just for that row? or something like that?

I'm not good at this.. Please help.

Thank you all

Solution

  • How about the following, it will prompt the user to enter a number, then it will look for that number on Row 1, if found then it will copy that column to columns W & Z:

    Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A
start:
x = InputBox("Enter a Column Number to copy to column W & Z", "Copy Column") 'get number from user
If IsNumeric(x) Then 'make sure input was number
Set foundcolumn = ws.Range("1:1").Find(What:=x) 'find number on row 1
    If Not foundcolumn Is Nothing Then 'if found
        ws.Range(ws.Cells(5, foundcolumn.Column), ws.Cells(LastRow, foundcolumn.Column)).Copy 'copy from row 5 to last on given column
        ws.Range("W5").PasteSpecial xlPasteValues 'paste in W5
        ws.Range("Z5").PasteSpecial xlPasteValues 'paste in Z5
    End If
Else
MsgBox "Please enter a number as in the first row"
GoTo start:
End If
End Sub