copy and paste to last row

[Patriot2879]

Hi i have the code below where i want to copy the word in Cell A2 in Shee1 which is 'ETA' and paste this into cell A3 Sheet1 down to the .LastRow but the below is not working can you help please?

Private Sub CommandButton4_Click()
Dim Lastrow As Long
ThisWorkbook.Sheets("Sheet1").Range("A2").Copy
Lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial
End Sub

 

[Joe4]

How can the last row be determined?
Obviously, we cannot do it by looking at column A, as that is the column we are populating.
Is there another column with data we can look at to determine where the data ends? If so, which column?

 

[Patriot2879]

hi this is the whole code below, but once all the below is populated I want to copy a2 to the last row once all th data is populated.

Private Sub CommandButton3_Click()
With Sheets("Sheet1")
        .Columns("C:C").Sort Key1:=.Range("C:C"), Order1:=xlDescending, Header:=xlYes
End With
 
 
  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("New")
    With copySheet
    .Range(.Cells(2, "C"), .Cells(.Cells(Rows.Count, "C").End(xlUp).Row, "C")).Copy
  End With
  pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
  
  With copySheet
   .Range(.Cells(2, "L"), .Cells(.Cells(Rows.Count, "L").End(xlUp).Row, "L")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
       With copySheet
   .Range(.Cells(2, "M"), .Cells(.Cells(Rows.Count, "M").End(xlUp).Row, "M")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
         With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 34).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
     
          With copySheet
   .Range(.Cells(2, "F"), .Cells(.Cells(Rows.Count, "F").End(xlUp).Row, "F")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 35).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
               With copySheet
   .Range(.Cells(2, "Q"), .Cells(.Cells(Rows.Count, "Q").End(xlUp).Row, "Q")).Copy
    End With
     pasteSheet.Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     
    Application.ScreenUpdating = False
    With Range("Q2", Range("Q" & Rows.Count).End(xlUp))
        .EntireColumn.Insert
        .NumberFormat = "@"
        With .Offset(, -1)
            .FormulaR1C1 = "=Text(RC[1],""dd/mm/YYYY"")"
            .Offset(, 1).Value = .Value
            .EntireColumn.Delete
        End With
    End With
    Application.ScreenUpdating = True




End Sub

 

[Joe4]

I want to copy a2 to the last row once all th data is populated.

Well, you really did not not answer my question (I asked you for a specific column reference). You provided code, but no data. Without knowing what the original data looks like, all I can really do is "guess", and I really prefer not to do that, as if there are gaps in your data, it would probably be an incorrect assumption and my solution probably would not work correctly.

Remember, we are not familiar with your problem. All that we have to go on is what you provide us here. So please provide with all the necessary details we need to help you, efficiently.

 

[Patriot2879]

Hi thanks for the email in Cell A2 I have the letters 'ETA' in that specific cell, the code above copies data from sheet1 into the sheet called 'New' for example data is copied over into column B, once all this is copied over I want cell A2 copied then pasted down to last row in Column A, to match the last row what was copied over into column B.
Hope this makes sense.

 

[Joe4]

Try this:

Sub FillColA()
    Dim lr As Long
'   Find last row in column B on sheet2 with data
    lr = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
'   Copy value from cell A2 to bottom of data on sheet 2
    Sheets("Sheet2").Range("A3:A" & lr) = Sheets("Sheet2").Range("A2")
End Sub

If you do not want it to be its own separate procedure, you can copy the body of it into the end of your code.

 

[Patriot2879]

hi sorry it didn't work the cell A2 is in sheet("New") which want to copy into the same sheet but into A3 to last row.

 

[Joe4]

I documented my code so hopefully you can see what is happening, can follow along, and make adjustments where necessary.
It sounds like if you just change all the sheets references of "Sheet2" to "New", it should work.

 

[Patriot2879]

Hi Yes that works great thank you for your help

 

[Joe4]

You are welcome.