| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can I copy "2" different Ranges from closed workbook (VBA)
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.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
opps wrong reference below.. the correct link
ishttp://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.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
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" wrote in message ... 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.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
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" wrote in message ... opps wrong reference below.. the correct link ishttp://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.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
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" wrote in message ... 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.... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
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" wrote in message ... 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" wrote in message ... 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.... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
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" wrote in message ... 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.... |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
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" wrote in message ... 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" wrote in message ... 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.... |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
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" wrote in message ... 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" wrote in message ... 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.... |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
can I copy "2" different Ranges from closed workbook (VBA)
You are welcome
Thanks for the feedback -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DarnTootn" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use "sumif" for cells "0" across multiple ranges? | Excel Discussion (Misc queries) | |||
| Protect "Copy" and "Save As" changes to workbook | Excel Discussion (Misc queries) | |||
| Macro crashes "Automation error" during copy of workbook with char | Excel Programming | |||
| conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
| compare ranges in different workbooks and copy "not matching values" at bottom of range 1 | Excel Programming | |||
Linear Mode
