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....

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

"DarnTootn" wrote:

> 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....

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.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DarnTootn" <(E-Mail Removed)> wrote in message news:67111107-FDF9-4C28-BCC6-(E-Mail Removed)...
>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....

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.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DarnTootn" <(E-Mail Removed)> wrote in message news:9D948485-2829-4D2A-9FA9-(E-Mail Removed)...
> opps wrong reference below.. the correct link
> is>>http://www.rondebruin.nl/ado.htm
>
> "DarnTootn" wrote:
>
>> 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....

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" wrote:

> 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.
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "DarnTootn" <(E-Mail Removed)> wrote in message news:67111107-FDF9-4C28-BCC6-(E-Mail Removed)...
> >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....
>

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DarnTootn" <(E-Mail Removed)> wrote in message news:5BAFB2C0-CCBD-4CF3-9547-(E-Mail Removed)...
>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" wrote:
>
>> 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.
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "DarnTootn" <(E-Mail Removed)> wrote in message news:67111107-FDF9-4C28-BCC6-(E-Mail Removed)...
>> >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....
>>

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




"DarnTootn" wrote:

> 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" wrote:
>
> > 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.
> >
> > --
> >
> > Regards Ron de Bruin
> > http://www.rondebruin.nl/tips.htm
> >
> >
> > "DarnTootn" <(E-Mail Removed)> wrote in message news:67111107-FDF9-4C28-BCC6-(E-Mail Removed)...
> > >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....
> >

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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DarnTootn" <(E-Mail Removed)> wrote in message news:B6747779-F591-43BA-B1A3-(E-Mail Removed)...
>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
>
>
>
>
> "DarnTootn" wrote:
>
>> 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" wrote:
>>
>> > 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.
>> >
>> > --
>> >
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl/tips.htm
>> >
>> >
>> > "DarnTootn" <(E-Mail Removed)> wrote in message news:67111107-FDF9-4C28-BCC6-(E-Mail Removed)...
>> > >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....
>> >

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" wrote:

> 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
>
> > ' For testing Copy the workbook name in Column E
> > sh.Cells(rnum + 1, "E").Value = FName(N)
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "DarnTootn" <(E-Mail Removed)> wrote in message news:B6747779-F591-43BA-B1A3-(E-Mail Removed)...
> >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
> >
> >
> >
> >
> > "DarnTootn" wrote:
> >
> >> 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" wrote:
> >>
> >> > 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.
> >> >
> >> > --
> >> >
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl/tips.htm
> >> >
> >> >
> >> > "DarnTootn" <(E-Mail Removed)> wrote in message news:67111107-FDF9-4C28-BCC6-(E-Mail Removed)...
> >> > >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....
> >> >
>

You are welcome

Thanks for the feedback

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DarnTootn" <(E-Mail Removed)> wrote in message news98D4562-5158-477D-B965-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> > ' For testing Copy the workbook name in Column E
>> > sh.Cells(rnum + 1, "E").Value = FName(N)
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "DarnTootn" <(E-Mail Removed)> wrote in message news:B6747779-F591-43BA-B1A3-(E-Mail Removed)...
>> >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
>> >
>> >
>> >
>> >
>> > "DarnTootn" wrote:
>> >
>> >> 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" wrote:
>> >>
>> >> > 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.
>> >> >
>> >> > --
>> >> >
>> >> > Regards Ron de Bruin
>> >> > http://www.rondebruin.nl/tips.htm
>> >> >
>> >> >
>> >> > "DarnTootn" <(E-Mail Removed)> wrote in message
>> >> > news:67111107-FDF9-4C28-BCC6-(E-Mail Removed)...
>> >> > >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....
>> >> >
>>