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

Domcast

New Member
Joined
Apr 19, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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")
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,155
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
col = Sheet3.Cells(11, Columns.Count).End(xlToLeft).Offset(, 1).Column
Sheet3.Cells(11, col).Value = Sheet2.Range("E10").Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,155
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,803
.
This macro works, assuming on the PASTE Sheet cell J11 has some data in it.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,297
Messages
5,577,245
Members
412,777
Latest member
MrGray
Top