Copy raw data into specific cells of a target sheet

Question

I have two worksheets within the same workbook, namely sheet1 ("rawdata") and sheet2 ("Overview).

I copy downloaded data into sheet1 ("rawdata"). Here the number of rows vary but heading/columns are always the same. After this I need to copy specific cells into another worksheet.

Here are the "rules" I was thinking about:

1) Always copy cells from the rawdata sheet E9, W9, X9 and Y9 into a specific cell in the target sheet. I had something like this (which worked):

Worksheets("overview").Range("X10").Value = Worksheets("rawdata").Range("E9").Value

2) Always copy the value within column E in the lastrow. However, the last row is varying from rawdata to rawdata while the column (E) stays the same. I tried something like this: (not working)

....= Worksheets("rawdata").Range("E1").End(xlDown).Value

3) The script should be linked to the button, when I click the button again to insert the data from the sheet rawdata, the data should be inserted in the next (following) column of worksheet overview.

possible duplicate of [Error Finding Last Used cell In VBA](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — David Zemens, Aug 10 '14 at 13:43
The most reliable way to find the last cell in a range can be found here: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — David Zemens, Aug 10 '14 at 13:43

score 2 · Answer 1 · edited May 23 '17 at 10:29

Assumes column E always has data. Which in this case should be true.

Sorry tried to simplify and broke it.

LastRow_WithDataInColumnE = Worksheets("rawdata").Range("E" & .Rows.Count).End(xlUp).Row

Should be

With Worksheets("rawdata")
    LastRow_WithDataInColumnE = .Range("E" & .Rows.Count).End(xlUp).Row
End With

Now .Rows.Count should refer to Worksheets("rawdata")

Worksheets("overview").Range("X10").Value = Worksheets("rawdata").Range("E" & .Rows.Count).End(xlUp).Row.Value

Should be

With Worksheets("rawdata")
    Worksheets("overview").Range("X10").Value = .Range("E" & .Rows.Count).End(xlUp).Row.Value
End With

There is a discussion here Error in finding last used cell in VBA. Suggests a better solution for situations where there is no data in Column E or where rows have been deleted.

Thank you very much. Indeed there is always data in columnE, however, the code is not working. In the "Rows.Count" the mistake has been detected with the message (invalid reference). :-( do you have ad hoc an idea why this message has been shown? — Sofo56, Aug 10 '14 at 13:59

Agus OZ · Answer 2 · 2019-12-18T16:18:43.847

You could do something like this to get the last data range in column E:

Public Function FindLastColumnECellAvailable()
    FindLastColumnECellAvailable = "E" & WorksheetFunction.CountA(Range("E:E"))
End Function

Then you will have this:

At the end just read the cell value:

Range(FindLastColumnECellAvailable).Value

Greetings

Sorry An apologize "in advance", I just read the date, hope this will help you yet or anyone else, it's my second day

Copy raw data into specific cells of a target sheet

2 Answers2