Link to home
Start Free TrialLog in
Avatar of wiredemc12
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.Worksheets(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?

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wiredemc12
wiredemc12

ASKER

I want to copy another range from another sheet within the same workbook. For example "Name2" range "C15", "M15")

Pseudo Code:
Set sourceSheet = customerWorkbook.Worksheets("Name2")
targetSheet.Range("A2", "K2").Value = sourceSheet.Range("C15", "M15").Value
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.Worksheets("Name2") so you don't have to type it every time, besides that there are other advantages using a variable.