Copy and paste macro command button

[erikbowie]

I need help with a macro. I need to copy cells from sheet 1 and paste to sheet 2. This will be used multiple times so i need the new past to go under the old past(ex.. copy from sheet 1 a1 and paste to sheet 2 c2 then c3,c4) after each button press.

 

[Leith Ross]

Hello erikbowie,

This macro will copy from "A1" on "Sheet1" and paste the contents and formats to "C1" on "Sheet2" then next cell will be "C2", etc. until the "Sheet2" is full.

Sub CopyAndPaste()

  Dim InputCell As Range
  Dim LastRow As Long
  Dim OutputCell As Range
  
    Set InputCell = Worksheets("Sheet1").Range("A1")
    Set OutputCell = Worksheets("Sheet2").Range("C1")
    
      LastRow = OutputCell.Parent.Cells(Rows.Count, OutputCell.Column).End(xlUp).Row
    
      If LastRow = Rows.Count Then
         MsgBox "Sheet is Full!", vbCritical, "Copy and Paste"
         Exit Sub
      End If
      
      If OutputCell.Row = LastRow Then
         InputCell.Copy Destination:=OutputCell
      Else
         InputCell.Copy Destination:=OutputCell.Offset(1, 0)
      End If
      
End Sub

 

[erikbowie]

thank you. the code seems to overwrite the cell on sheet 2 c1. i need it to go to c2 on the next button click. every time i click the command button i need it to go to next cell on sheet 2. ive been working on this for a week now and just cant wrap my brain around it. your code has moved me in thr right direction.

 

[Leith Ross]

Hello erikbowie,

My mistake. This will start at C2.

Sub CopyAndPaste()
 
 Dim InputCell As Range
  Dim LastRow As Long
  Dim OutputCell As Range
  
    Set InputCell = Worksheets("Sheet1").Range("A1")
    Set OutputCell = Worksheets("Sheet2").Range("C2")
    
      LastRow = OutputCell.Parent.Cells(Rows.Count, OutputCell.Column).End(xlUp).Row
    
      If LastRow = Rows.Count Then
         MsgBox "Sheet is Full!", vbCritical, "Copy and Paste"
         Exit Sub
      End If

      If LastRow = OutputCell.Row And OutputCell = "" Then 
         InputCell.Copy Destination:=OutputCell
      Else
         InputCell.Copy Destination:=OutputCell.Offset(1, 0)
      End If

End Sub