Hi to Everyone,
I have found this code online and modified to the best knowledge I know in VBA. I have two scenarios of copying and pasting data, the below is the first one.
I want the DB_ForReference to be dynamic based on ActiveSheet.Range ("I1") as I want the user to be able to select from drop down to change the sheet location where the data will be imported. I have several DB files and I want the user to select from it prior importing the data to wsDest. I tried adding Dim dbDest As string and replace the "DB_ForReference" - to my surprise no luck
.
The second scenario will be to copy the data from wsCopy and past it in wsDest but I have to set VBA to select which column to add. This code will be for "Issued For Acceptance". As you see in the All_IFC - Master Register.xlsm. I want to populate the data from Import Excel File and paste it from there but the column arrangements are different.
Lastly, is it possible to copy and paste the values in wsDest without opening the workbook "All_IFC - Master Register.xlsm"?
I have three excel files, as follows: Please download the file in below link.
1. Raw Data (from a certain standard register)
2. Import Excel file - (were the Raw Data is transformed to make a proper data-set prior import to the Database using Power Query). This is where the below code VBA will run.
3. DB Database (several DB sheets for each type of document)
https://www.dropbox.com/sh/kxinnpxoobwt4wx/AAACB9vTJaevRH8qbMVlo9DYa?dl=0
Private Sub ImportData_ForReferenceOnly_Click()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim dbDest As String
dbDest = ActiveSheet.Range("I1")
Set wsCopy = Workbooks("SAMPLE_.xlsm").Worksheets("Query")
Set wsDest = Workbooks("All_IFC - Master Register.xlsm").Worksheets("DB_ForReference")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
wsCopy.Range("A14:F" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)
MsgBox ("Data is imported")
End Sub
2.5.0.0
I have found this code online and modified to the best knowledge I know in VBA. I have two scenarios of copying and pasting data, the below is the first one.
I want the DB_ForReference to be dynamic based on ActiveSheet.Range ("I1") as I want the user to be able to select from drop down to change the sheet location where the data will be imported. I have several DB files and I want the user to select from it prior importing the data to wsDest. I tried adding Dim dbDest As string and replace the "DB_ForReference" - to my surprise no luck
. The second scenario will be to copy the data from wsCopy and past it in wsDest but I have to set VBA to select which column to add. This code will be for "Issued For Acceptance". As you see in the All_IFC - Master Register.xlsm. I want to populate the data from Import Excel File and paste it from there but the column arrangements are different.
Lastly, is it possible to copy and paste the values in wsDest without opening the workbook "All_IFC - Master Register.xlsm"?
I have three excel files, as follows: Please download the file in below link.
1. Raw Data (from a certain standard register)
2. Import Excel file - (were the Raw Data is transformed to make a proper data-set prior import to the Database using Power Query). This is where the below code VBA will run.
3. DB Database (several DB sheets for each type of document)
https://www.dropbox.com/sh/kxinnpxoobwt4wx/AAACB9vTJaevRH8qbMVlo9DYa?dl=0
Private Sub ImportData_ForReferenceOnly_Click()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim dbDest As String
dbDest = ActiveSheet.Range("I1")
Set wsCopy = Workbooks("SAMPLE_.xlsm").Worksheets("Query")
Set wsDest = Workbooks("All_IFC - Master Register.xlsm").Worksheets("DB_ForReference")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
wsCopy.Range("A14:F" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)
MsgBox ("Data is imported")
End Sub
2.5.0.0
