CODEWITHSUNDEEP
excelvba
Katia
Katia

Reputation: 53

Excel VBA move values from one sheet to another

I need a suggestion. I have ~50 tables which are pasted one after another in one Excel sheet and I need to copy data from particular cell and paste to another workbook to another cell.

Now I'm using VBA to achieve this: Assign value from cell to "number" number = data.Cells(500, "E").Value and then assign "number" to other cell result.Cells(3, "D") = number. I need to do it with every table and now I'm just use the row number and column letter to determine the value. Here is an example how my tables looks like:

table

And my VBA code:

number_e = data.Cells(80, "E").Value
result.Cells(8, "D") = number_e

number_j = data.Cells(80, "L").Value
result.Cells(9, "D") = number_j

number_n = data.Cells(80, "P").Value
result.Cells(10, "D") = number_n

number_e = data.Cells(500, "E").Value
result.Cells(3, "D") = number_e

number_j = data.Cells(500, "J").Value
result.Cells(4, "D") = number_j

number_n = data.Cells(500, "N").Value
result.Cells(5, "D") = number_n

<...>

number_e = data.Cells(4385, "E").Value
result.Cells(242, "D") = number_e

number_j = data.Cells(4385, "L").Value
result.Cells(243, "D") = number_j

number_n = data.Cells(4385, "P").Value
result.Cells(244, "D") = number_n

In some tables the desired values are in different columns.

I believe there is more rational way to do this... Also I'm using values from one table one row but need to use more values from other rows and I don't want to go through writing every row number again...

Maybe I should convert data to tables and use references but I'm not sure if that can help.

Thank you for any suggestions.

Upvotes: 0

Views: 938

Answers (1)

Rahul Chawla
Rahul Chawla

Reputation: 1078

' Checking number of records

rCount = data.Cells(.Rows.Count, 1).Row

' Copying the data

for i=1 to rCount
    result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,5).Value
    result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,10).Value
    result.Cells(Cells(.Rows.Count,4).End(xlUp).row+1,4).value = data.cells(i,14).Value
next i

Upvotes: 1

Related Questions