Macro not pasting in values

Question

I have a macro that pastes values from one sheet to another (controlos -> database) populating the first unpopulated row, and it works mostly fine. However it doesn't copy & paste values from cell D10 on sheet1 (controlos) to the last unpopulated cell on the D column. After it supposedly does that, it clears out the cells on the first sheet to register another. The problematic part:

*Sheets("controlos").Select
    Range("D10").Select
    ActiveSheet.Paste
    Selection.Copy
    Sheets("database").Select
    Range("D1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Sheets("controlos").Select*

Hello, i just answered something like your question [here](https://stackoverflow.com/questions/55120472/how-to-copy-and-paste-data-so-specific-row/55121072#55121072) check it out for if it helps. — Damian, Mar 12 '19 at 12:10
Don't use select, reference the sheets, so `set ws1=worksheets("sheet1")` then you can say `ws1.range("d10").value=wsCopyFrom.range("d10").value` for example. — Nathan_Sav, Mar 12 '19 at 12:11

score 0 · Answer 1 · answered Mar 12 '19 at 12:13

0

The below code is an easy way to copy paste from Sheet 1, range A1 to Sheet 2 range A1:

Option Explicit

Sub test()

    Dim ws1 As Worksheet, ws2 As Worksheet

    With ThisWorkbook

        Set ws1 = .Worksheets("Sheet1")
        Set ws2 = .Worksheets("Sheet2")

        ws1.Range("A1").Copy ws2.Range("A1")

    End With

End Sub

answered Mar 12 '19 at 12:13

Error 1004

7,877
3
23
46

That method works but only for specific cells. In my particular case, I would need to be adding to the last unpopulated cell (on the last unpopulated row). – reallybadprogrammer Mar 12 '19 at 12:18

score 0 · Answer 2 · answered Mar 12 '19 at 12:31

I would suggest if you are trying to find the last unpopulated cell in a sheet to use (rows.count). This will count from the bottom of the sheet to the first populated cell and then go down 1. Then it will take the value of Sheet1 cell D10 and put it in the next available cell in Sheet2.

sub FindLastCellInColumn

     dim row as long, ItemToCopy as string

     ItemToCopy = Sheets("Sheet1").Range("D10").Value

     Row1 = Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row + 1

     Sheets("Sheet2").Range("D" & Row1).Value = ItemToCopy

End Sub

Macro not pasting in values

2 Answers2