Find next empty cell in Row 1 and copy paste from another Worksheet

[SwiftM]

Hi all,

I am really struggling with this, and it's something I worked out in the past but for the life of me cannot work it out again!

I have two worksheets. ws = "Crime" and ws1 = "Log".

In worksheet "Log", in cell "A1" I have typed in "Log No" (which is permanent)

What I am trying to do is when I click a button in "Crime", it finds the next empty cell in Row 1 in "Log" and copies and pastes the value from cell "B2" from worksheet "Crime".

I have tried this code but for some reason inserts the copied value into A11!

Sub Last_Cell()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Sheets("Crime")
Set wsDest = Sheets("Log")


lDestLastRow = wsDest.Cells(1, "A").End(xlUp).Offset(0, 1).Row


wsDest.Range("A1" & lDestLastRow).Value = wsCopy.Range("B2")


End Sub

Obviously, I am doing something wrong so any help would be gratefully appreciated.

Many thanks.
M

 

[DanteAmor]

I'm not understanding if you want to copy to the right or down.
It would be clearer if you give examples.

I put the 2 macros to copy to the right and down. Use the one you need.

Sub copy_to_the_right()
  Sheets("Log").Cells(1, Columns.Count).End(1)(1, 2).Value = Sheets("Crime").Range("B2").Value
End Sub

Sub copy_down()
  Sheets("Log").Cells(Rows.Count, 1).End(3)(2).Value = Sheets("Crime").Range("B2").Value
End Sub

-----
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[SwiftM]

I'm not understanding if you want to copy to the right or down.
It would be clearer if you give examples.

I put the 2 macros to copy to the right and down. Use the one you need.

Sub copy_to_the_right()
  Sheets("Log").Cells(1, Columns.Count).End(1)(1, 2).Value = Sheets("Crime").Range("B2").Value
End Sub

Sub copy_down()
  Sheets("Log").Cells(Rows.Count, 1).End(3)(2).Value = Sheets("Crime").Range("B2").Value
End Sub

-----
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

Fantastic! Thank you Dante!

Apologies, it was copy to the right which i failed to mention. Both work great though..

Thanks again,
M