can I copy "2" different Ranges from closed workbook (VBA)

[DarnTootn]

I am using Ron DeBruins code to accomplish my original task. (works great)
http://www.rondebruin.nl/copy1.htm#workbook
But recently more data is needed from a second sheet within the same
workbook(s) that I am retreiving my data from. Is there a way to add code
to this example to include a second range of data from the same workbook?

Thank you in advance for the help....

 

[DarnTootn]

opps wrong reference below.. the correct link
is>>http://www.rondebruin.nl/ado.htm

 

[Ron de Bruin]

Hi DarnTootn

Yes that is possible

Post the two ranges then I will reply with a working example
Do you want to copy them (the two ranges) below each other or next to each other.

 

[Ron de Bruin]

http://www.rondebruin.nl/ado.htm

You can repeat this line with a different range and dest cell

GetData ThisWorkbook.Path & "\test.xls", "Sheet1", _
"A1:C5", Sheets("Sheet1").Range("A1"), True, True

Tell me in which example you want to use it in the example workbook if
you can't make it work.

 

[DarnTootn]

I am referencing your
Sub GetData_Example5()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, destrange As Range
Dim sh As Worksheet

"Code was deleted from here down to here"

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData FName(N), "STATS", "A2:AD2", destrange, False, False
GetData FName(N), "Billing Sheet", "J42:K43"<< this is the new
DATA needed......

 

[Ron de Bruin]

You can try this

Because the first range is one row we use offset to go one row down to paste the new data

GetData FName(N), "Billing Sheet", "J42:K43", destrange.Offset(1, 0), False, False

 

[DarnTootn]

I need to copy the new data on the same line as the original data.. and
actually I do not need All the info just the J42 cell...(i should just write
it as J42:J42) anyway here is the code I have.. So I would need the Data from
J42 on the Billing sheet to go to AE2 on the new worksheet that this code
generates.. so what I want it to do is basically add one more Cell on the
same line....
So I need the DATA from J42 to go to AE2 on the new worksheet along with the
other DATA from the getdata I have below...

Sub GetData_Example5()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, destrange As Range
Dim sh As Worksheet

SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
' Sort the Array
FName = Array_Sort(FName)

Application.ScreenUpdating = False
'Add worksheet to the Activeworkbook and use the Date/Time as name
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = Format(Now, "mm-dd-yy h-mm-ss")

'Loop through all files you select in the GetOpenFilename dialog
For N = LBound(FName) To UBound(FName)

'Find the last row with data
rnum = LastRow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' For testing Copy the workbook name in Column E
sh.Cells(rnum + 1, "E").Value = FName(N)


'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData FName(N), "STATS", "A2:AD2", destrange, False, False
Next

End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Try this then

GetData FName(N), "STATS", "A2:AD2", destrange, False, False
GetData FName(N), "Billing Sheet", "J42:J42", destrange.Offset(0, 32), False, False

Change the 32 to the column if it is not the correct one

Change this also

 

[DarnTootn]

Thank you Ron your a life saver... That works with a little tweak... (0, 30)
was the only change that I had to make... THANK YOU THANK YOU!!!!!!!!!!

 

[Ron de Bruin]

You are welcome

Thanks for the feedback