I have a control on a customer's form that copies a template xlsx file and fills it with customer name, address, etc. While it is open, I want to copy a cell's value from one xlsx file and paste it into a cell on the open xlsx file that I just created.
After trying various suggestions seen on the 'net for 12 weeks, I am asking this great forum, once again, for help. Did learn a lot during the time spent, though.
Here is my code:
Private Sub SOMCreate_Label_Click()
Dim appExcel As Excel.Application 'For creating the destination file
Dim lngLastDataRow As Long 'That code is not shown, here.
Dim Folder_Path As String
Dim strFolder_PathNew As String
Dim strFolder_PathSrc As String
'There is code here that creates the destination file: L_Name SOM.xlsx , ' from a 'customers form' in my db, & leaves it open.
'I have a source file: L_Name Roof.xlsx' that contains a cell with the ' "matlcost" value.
'I want to copy the 'matlcost' value in cell "M59" of the source file to cell ' "E14" on the open destination file.
'The following code is 1 of the zillion versions that I tried that didn't work.
Dim srce As Workbook 'source
Dim dstn As Workbook 'destination
strFolder_PathSrc = strFolder_Path & "" & Me.[L_Name] & " Roof" &_ ".xlsx"
strFolder_PathNew = strFolder_Path & "" & Me.[L_Name] & " SOM" &_ ".xlsx"
Set srce = Workbooks.Open(strFolder_PathSrc) 'Opens the source file
'Copies from the source worksheet to the destination worksheet.
Worksheets("Sheet1").Range("M59").Value = srce.Worksheets_("Sheet1").Range("E14").Value
'Closes the source file, leaving the destination file open with the 'matlcost ' ' value added.
srce.Close False
.End Sub
After trying various suggestions seen on the 'net for 12 weeks, I am asking this great forum, once again, for help. Did learn a lot during the time spent, though.
Here is my code:
Private Sub SOMCreate_Label_Click()
Dim appExcel As Excel.Application 'For creating the destination file
Dim lngLastDataRow As Long 'That code is not shown, here.
Dim Folder_Path As String
Dim strFolder_PathNew As String
Dim strFolder_PathSrc As String
'There is code here that creates the destination file: L_Name SOM.xlsx , ' from a 'customers form' in my db, & leaves it open.
'I have a source file: L_Name Roof.xlsx' that contains a cell with the ' "matlcost" value.
'I want to copy the 'matlcost' value in cell "M59" of the source file to cell ' "E14" on the open destination file.
'The following code is 1 of the zillion versions that I tried that didn't work.
Dim srce As Workbook 'source
Dim dstn As Workbook 'destination
strFolder_PathSrc = strFolder_Path & "" & Me.[L_Name] & " Roof" &_ ".xlsx"
strFolder_PathNew = strFolder_Path & "" & Me.[L_Name] & " SOM" &_ ".xlsx"
Set srce = Workbooks.Open(strFolder_PathSrc) 'Opens the source file
'Copies from the source worksheet to the destination worksheet.
Worksheets("Sheet1").Range("M59").Value = srce.Worksheets_("Sheet1").Range("E14").Value
'Closes the source file, leaving the destination file open with the 'matlcost ' ' value added.
srce.Close False
.End Sub