Adapt code to goto last empty column and run again

[Chewyhairball]

Hi

I have this bit of code someone did for me that copies data from one worksheet to another.
copies the data into Cell A3.

This works great for 1 worksheet and if you use it on a different worksheet then it just overides the data which is what I wanted.

I would like to be able to add data from additional worksheets without over riding but instead going to the last populated cell in row5, offset up 2 cells and paste the data there.
If i could do this as many times as I wanted it would be perfect.

So if it was the first time i copied data into it it goes to cell A3 as in the code but any subsequent imports goto as described above.

thanks in advance for any help.

Sub Copy_Stats()
'
    Dim srcWB As Workbook
    Dim srcSht As Worksheet
    Dim srcRng As Range
    Dim destWB As Workbook
    Dim destSht As Worksheet
    
    Set srcWB = ActiveWorkbook
   
    Set srcSht = srcWB.Worksheets("Controls")
    
    Set srcRng = srcSht.Range("A1:DZ321")
    
    Set destWB = ThisWorkbook
    Set destSht = destWB.Worksheets("Data from Stats")
    
    Application.ScreenUpdating = False
    
    destSht.Range("A3").Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value = srcRng.Value
  
    srcWB.Close savechanges:=False
   Application.ScreenUpdating = True
    Sheets("stats review form").Select

MsgBox "Data successfully imported." & Chr(10) & Chr(10) & "Note: It is recommended the file is saved at this point."

    
    Exit Sub

    
End Sub

 

[Chewyhairball]

managed to use this bit of code and it seems to do what I want

destSht.Range("XFD5").End(xlToLeft).Offset(-2, 0).Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value = srcRng.Value