Excel VBA code to copy paste a column from a form to a row in consolidated spreadsheet

Question

I have been trying to create code for the following.

1. Data is captured on a excel spreadsheet designed as a form. The date is captured in column range B1-B48

2. I want to transfer that to our summary spreadsheet where we hold the data for each form completed in rows. (summary sheet-testing).

3. The data should be pasted transposed and find the next blank line and not over write any other entries.

Problems with my code are:-

1. in my summary sheet testing the data is writing to row 49. So its usng the last cell in the 'form' to determine where to paste the data. Rather than the next empty row.

2. the data is not being pasted transposed to the next empty line it is always over writing row 49

This is my first go at this be gentle.

Paul

---

Private Sub CommandButton1_Click()

ActiveSheet.Range("B1:B48").Copy
Workbooks.Open Filename:="https://catsprotection.sharepoint.com/sites/Crawley/FinanceDocuments/Adoptions/Testing Area/summary sheet - testing.xlsx"
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
If eRow >= 1 Then eRow = eRow + 1
ActiveSheet.Cells(eRow, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False

End Sub

Answer 1

The issue is that you don't specify a concrete workbook or worksheet. Avoid using ActiveWorkbook and ActiveSheet this is not very reliable and can bring up different results. Instead always define a concrete sheet (eg by its name). Also see How to avoid using Select in Excel VBA.

Also I recommend to use Option Explicit and declare all your variables properly to avoid any issues.

Option Explicit

Private Sub CommandButton1_Click()
    Dim DestWb As Workbook 'define destination workbook
    Set DestWb = Workbooks.Open(Filename:="https://catsprotection.sharepoint.com/sites/Crawley/FinanceDocuments/Adoptions/Testing Area/summary sheet - testing.xlsx")

    Dim DestWs As Worksheet 'define destination worksheet
    Set DestWs = DestWb.Sheet1

    Dim eRow As Long 'determine last row in destination worksheet
    eRow = DestWs.Cells(DestWs.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    If eRow >= 1 Then eRow = eRow + 1

    ThisWorkbook.Worksheets("MyFormSheet").Range("B1:B48").Copy 'copy directly before paste
    DestWs.Cells(eRow, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True

    Application.CutCopyMode = False

    DestWb.Close SaveChange:=True 'close and save destination workbook
End Sub