Copy & Paste to the next empy cell. (Vba Code not Working)

[Domcast]

I created a macro button to copy a cell and paste value in another sheet but it just replaces the value in the same cell on the other sheet. I need it to count the columns and continue to paste the value in the next cell to the right. The code below is what i currently using to make it happen, this worked on prior spreadsheets I have done but not sure what I'm doing wrong. Please Help..



Col = Sheet3.Cells(11, 11).CurrentRegion.Columns.Count + 1
Sheet3.Cells(11, 11) = Sheet2.Range("E10")

 

[Fluff]

Hi & welcome to MrExcel.
How about

col = Sheet3.Cells(11, Columns.Count).End(xlToLeft).Offset(, 1).Column
Sheet3.Cells(11, col).Value = Sheet2.Range("E10").Value

 

[Fluff]

Cross posted Copy & Paste to the next empy cell. (Vba Code not Working) - OzGrid Free Excel/VBA Help Forum
and Copy & Paste to the next empy cell. (Vba Code not Working)

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Logit]

.
This macro works, assuming on the PASTE Sheet cell J11 has some data in it.

Sub CopyRange()

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet1")  'change sheet name to suit
Set destination = Sheets("Sheet2")  'change sheet name to suit
'emptyColumn = 11
'find empty Column (actually cell in Row 1)'
emptyColumn = destination.Cells(11, destination.Columns.Count).End(xlToLeft).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If

source.Range("E10").Copy destination.Cells(11, emptyColumn)

End Sub