VBA code to copy and paste range into next empty row on another sheet

[Katyjean1]

I found this code to copy/paste a range from sheet 1 to sheet 2 onto the next empty row. It was working great until I switched to office 365. Now it won't paste onto the next empty row. Can anyone tell me what's wrong with it? Thanks!

 Sub SUBMIT2()
 
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet


  Set copySheet = Worksheets("Entry Form")
  Set pasteSheet = Worksheets("Distribution")


  copySheet.Range("F16:P26").Copy
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
  Application.CutCopyMode = False
  
End Sub

 

[alansidman]

Seems ok to me. Are you getting any error messages? What happens when you step through the code, ie. F8 ?

Open the VBE. Click on Tools. References. Do any of the lines indicate you are missing a reference. If so, fix it by scrolling down to the reference and add it in.

 

[Katyjean1]

No errors are indicated when I step through the code. Each line is highlighted in yellow, but no missing references. I don't understand why this code stopped working after I switched to Office 365. It still pastes values but it doesn't paste onto the next empty row.

 

[Fluff]

Hi & welcome to MrExcel
In that case, where is it pasting the data?

 

[Katyjean1]

On top of the previously pasted data. I think I just realized what the error is. The row I'm pasted doesn't always have data in column A. Is that what's causing the error? Since the data in column A doesn't always have data it's being considered as the next empty row?

 

[Katyjean1]

Do you know a way around that?

 

[Katyjean1]

If it helps, the one column that will always have data is column #10

 

[alansidman]

Try this:

 Sub SUBMIT2()
 
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Dim lr As Long
  


  Set copySheet = Worksheets("Entry Form")
  Set pasteSheet = Worksheets("Distribution")




  copySheet.Range("F16:P26").Copy
  lr = pasteSheet.Range("J" & Rows.Count).End(xlUp).Row
  pasteSheet.Range("A" & lr + 1).PasteSpecial xlValues
  Application.CutCopyMode = False
  
End Sub

 

[Katyjean1]

Works like magic! Thanks so much!!