0

i can copy whole rows but finding it difficult to locate the end cell of the row N and then copy everything from N2 to last the row. The end of the row - N ( cell) changes in length as the data imported changes

enter image description here

Sub Copy_To_Lastrow()

    Application.ScreenUpdating = False
    Dim Lastrow As Long

    Sheets("Meeting1").Select
    Range("N2").Select

    Lastrow = Cells(Rows.Count, "AN").End(xlUp) + 1
    Range("n2").Copy Cells(Lastrow, "AN")
    'Lastrow = Cells(Rows.Count, "AN").End(xlUp).Row + 1
    'Range("n2").Copy Cells(Lastrow, "AE")
    'Lastrow.PasteSpecial xlPasteValues
     Range(Lastrow).PasteSpecial.Values

     Application.ScreenUpdating = True
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
ToddV
  • 1
  • 1

1 Answers1

0

One way, avoiding any copy/paste:

Sub Copy_To_Lastrow()
    Dim lr As Long
    With Worksheets("Meeting1")    '<<should specify a workbook here...
        lr = .Cells(.Rows.Count, "AN").End(xlUp).Row
        .Range("N2:N" & lr).Value = .Range("N2").Value
    End With
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • this copies over the 1st row Cell N1 and leaves all the others except N2 blank. I was trying to copy everything in N2 cell.value to to the end of specified i.e. - last cell in column n – ToddV Jan 24 '23 at 00:51
  • You used column AN to find the last-occupied row in your posted code: `Lastrow = Cells(Rows.Count, "AN").End(xlUp)` so I was assuming that was correct. You could use any other fully-populated column instead. – Tim Williams Jan 24 '23 at 00:58
  • ok. thanks. Yes my code was just not working and i was looking in the row whereas i should be looking in the column - cell.value. And i am not sure what the answer is right now. – ToddV Jan 24 '23 at 01:30
  • If you just need to know the last occupied row on the sheet you can use something like https://stackoverflow.com/a/63521561/478884 If that's not the problem then I'm not sure what to suggest. – Tim Williams Jan 24 '23 at 02:58
  • the challenge is that i need to copy the value in N2 to the last cell in the range which dynamically updates in size. So first i need to find the cell that is blank in Row N then copy the value from N2 into this value and then repeat this to the end of the range. – ToddV Jan 24 '23 at 23:50
  • So "fill down N2 to the end of the column" but what exactly determines the "end" of the column ? Would it be the same row as the last value in some other column? If not that then what? – Tim Williams Jan 24 '23 at 23:57
  • In the example sheet above the last row is the last row. – ToddV Jan 25 '23 at 04:26
  • 1
    yes - very simple - amended as follows :With Sheets("Meeting1") .Range("N2").AutoFill .Range("N2:N" & .Cells(.Rows.Count, "M").End(xlUp).row) End With – ToddV Jan 25 '23 at 05:16