CODEWITHSUNDEEP
excelcopy-pastevba
Abyrvalg
Abyrvalg

Reputation: 3

Copy specific cells from one row and paste into different cells on another worksheet

I saw many similar questions on this site but non of them has answer. Not sure why. Probably it is too simple to bother. Not for me though. I am not good at all with VBA and will not probably need to pick on it for another number of years. Hopefully someone will be kind to spend time and help.

I have two sheets in a workbook. Sht1 contains data organized by rows. Rows populated daily, total number of rows will be 300 to 400 by the end of project. Sht2 represents a document form. Most of the cells on that form contain static information that does not change from one report to another. Except some dynamic cells that have to be populated from Sht1. I print the form and file the hard copy. I might come back and print some reports one more time if the hard copies gone missing or the data changed for some reason. The point is clear - I do not want to keep and manage 400 Word files. It is just painful.

What I wanted is assign a code to a command button which will call for an input box. I enter the row ID (I guess the rows should be numbered consequently from 1 to N). Then VBA takes data from some cells of that row, lets say C5 (when ID=4), E5 and H5 on Sht1 and copies them to cells B5, D5 and D7 on Sht2.

Much appreciated for your time reading this and even more if you can help. Thank you.

Upvotes: 0

Views: 6283

Answers (1)

stifin
stifin

Reputation: 1400

Here is some very simple code to copy data from one cell on Sheet1 to another cell on Sheet2.

Sub Macro1()

    Dim iRow As Integer

    iRow = InputBox("Which row?")

    Worksheets("Sht2").Cells(5, 2).Value = Worksheets("Sht1").Cells(iRow, 3).Value
    Worksheets("Sht2").Cells(5, 4).Value = Worksheets("Sht1").Cells(iRow, 5).Value
    Worksheets("Sht2").Cells(7, 4).Value = Worksheets("Sht1").Cells(iRow, 7).Value

End Sub

This takes values from the row specified on Sheet1, columns C, E, and H. It copies these values onto Sheet2, cells B5, D5, D7, respectively. Note the order of arguments to Cells(row, col), which is opposite to the Excel cell references that you would be used to. In other words, remember that Cells(1, 3) is actually C1.

This is about as simple as I can make it but it should set you in the right direction.

Upvotes: 1

Related Questions