wiredemc12
asked on
vba excel import data from another workbook
I would like to use the worksheet names within the workbook to select the ranges vs the object ( I think that is what the code is using)See line 27.
Currently the code is Set sourceSheet = customerWorkbook.Worksheet s(12)
I would like to make it : Set sourceSheet = customerWorkbook. “Name”
Also how would I write the code to select another range in a different worksheet within the same sourceSheet?
Currently the code is Set sourceSheet = customerWorkbook.Worksheet
I would like to make it : Set sourceSheet = customerWorkbook. “Name”
Also how would I write the code to select another range in a different worksheet within the same sourceSheet?
Sub getworkbook()
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = "Text files (*.xlsm),*.xlsm"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
On Error GoTo Error1
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
'instead of using worksheet 12 I would like to set it to the name of the worksheet
Set sourceSheet = customerWorkbook.Worksheets(12)
targetSheet.Range("A1", "K1").Value = sourceSheet.Range("C13", "M13").Value
' Close customer workbook
customerWorkbook.Close
Error1:
Exit Sub
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your pseudo code will work just fine, as long as the 2 ranges you copy from and to have the same size.
You can change the sourceSheet reference within the code as many times as required.
The variable sourceSheet is just a container holding the reference customerWorkbook.Worksheet s("Name2") so you don't have to type it every time, besides that there are other advantages using a variable.
You can change the sourceSheet reference within the code as many times as required.
The variable sourceSheet is just a container holding the reference customerWorkbook.Worksheet
ASKER
Pseudo Code:
Set sourceSheet = customerWorkbook.Worksheet
targetSheet.Range("A2", "K2").Value = sourceSheet.Range("C15", "M15").Value