thanhvanchi
12-12-2016, 01:04 AM
Hello everyone!
First and foremost - I'm rather new to VBA, and have only scratched the surface of the wonderful potential that VBA offers. Therefore, pardon me if my description might lack a bit
Currently (in MS Excel 2010), I am trying to create 3 x userforms where I have a GUI to select a specific excel file from a path directory, and then the whole path is pasted and shown in a textbox in each of the three userforms.
All of this is working flawlessly.
Now when I want to click my command button to open up all three different workbooks to copy a specific data range and paste it into my current sheet, it merely just freezes Excel (I get no error message).
Below is shown a code example of my VBA module to go through the operation - Here utilizing the path that I pasted in a textbox from one of the userforms (as a note here - I named each textbox from each userform as textbox1, textbox2 and textbox3 to avoid confusion).
Sub Update_CM_Eng()
Dim wbSource As Workbook
Dim wbDestination As Workbook
'open the source workbook from textbox value from userform and select the source sheet
Set wbSource = Workbooks.Open(TextBox1.Value)
'Set the destination workbook variable
Set wbDestination = Workbooks("Recruitment performance.xlsm")
'copy the source range
wbSource.Sheets("recruit_data").Range("$A$2:$N$150").Copy
'paste the value at range
wbDestination.Sheets("Applicants_CM_Eng").Range("A2:N150").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
ActiveWorkbook.Save
Workbooks("recruit_data.xls").Close SaveChanges:=False
Sheets("Total Overview").Select
End Sub
Essentially when I click my command button, I call upon the three modules that I've created (code example as shown above).
My question is therefore: How do I set my source workbook (wbSource) as the workbook that I specified in the textbox value from a userform in the above code?
Thank's a lot!
First and foremost - I'm rather new to VBA, and have only scratched the surface of the wonderful potential that VBA offers. Therefore, pardon me if my description might lack a bit
Currently (in MS Excel 2010), I am trying to create 3 x userforms where I have a GUI to select a specific excel file from a path directory, and then the whole path is pasted and shown in a textbox in each of the three userforms.
All of this is working flawlessly.
Now when I want to click my command button to open up all three different workbooks to copy a specific data range and paste it into my current sheet, it merely just freezes Excel (I get no error message).
Below is shown a code example of my VBA module to go through the operation - Here utilizing the path that I pasted in a textbox from one of the userforms (as a note here - I named each textbox from each userform as textbox1, textbox2 and textbox3 to avoid confusion).
Sub Update_CM_Eng()
Dim wbSource As Workbook
Dim wbDestination As Workbook
'open the source workbook from textbox value from userform and select the source sheet
Set wbSource = Workbooks.Open(TextBox1.Value)
'Set the destination workbook variable
Set wbDestination = Workbooks("Recruitment performance.xlsm")
'copy the source range
wbSource.Sheets("recruit_data").Range("$A$2:$N$150").Copy
'paste the value at range
wbDestination.Sheets("Applicants_CM_Eng").Range("A2:N150").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
ActiveWorkbook.Save
Workbooks("recruit_data.xls").Close SaveChanges:=False
Sheets("Total Overview").Select
End Sub
Essentially when I click my command button, I call upon the three modules that I've created (code example as shown above).
My question is therefore: How do I set my source workbook (wbSource) as the workbook that I specified in the textbox value from a userform in the above code?
Thank's a lot!