delpollard1982
New Member
- Joined
- Apr 27, 2021
- Messages
- 3
- Office Version
- 2016
- Platform
- Windows
Hi,
I'm struggling with getting the VBA code correct for the following.
The requirement is to copy data from an Active workbook via a command button trigger to a data register which you can see in the code below is named "Disposal Register".
The Disposal Register name will stay constant.
The Active workbook, where the data is exported from will be variable and will have no constants, however in the code below it is named "Disposal Form - With Macros".
Thankyou in advance.
Del
I'm struggling with getting the VBA code correct for the following.
The requirement is to copy data from an Active workbook via a command button trigger to a data register which you can see in the code below is named "Disposal Register".
The Disposal Register name will stay constant.
The Active workbook, where the data is exported from will be variable and will have no constants, however in the code below it is named "Disposal Form - With Macros".
Thankyou in advance.
Del
VBA Code:
Private Sub CommandButton8_Click()
Workbooks.Open "X:\Departments\ALL DEPARTMENTS\New Disposal Process\Disposal Register.xlsx"
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Workbooks("Disposal Form - With Macros.xlsm").Worksheets("Data")
Set wsDest = Workbooks("Disposal Register.xlsx").Worksheets("Data")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
Workbooks("Disposal Form - With Macros.xlsm").Worksheets("Data").Range("A1:X1" & lCopyLastRow).Copy _
Workbooks("Disposal Register.xlsx").Worksheets("Data").Range("A" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
Workbooks("Disposal Register.xlsx").Close SaveChanges:=True
End Sub