| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ron de Bruin's Merge - Can I use multiple named ranges??
Hi All
Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Does this help?:
SourceRng:=Range("A5:D5") Set sourceRange = Range("A1:C1") "Andrew P." wrote: Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Hi Andrew
Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Hi Ron
I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Hi Gleam
Sorry, I didnt explain myself well. The reference in there (Range("A5:D5")) was the original example only, what I needed was lots of named ranges (around 40)to take their place. From what I can work out, the merge seems to want just one range. Regards Andrew "Gleam" wrote: Does this help?: SourceRng:=Range("A5:D5") Set sourceRange = Range("A1:C1") "Andrew P." wrote: Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Are they all one cell the named ranges ?
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Hi Ron
Except for a few single cells, they are all merged cells, for example: ASC_Account = B9:C9 Fault = B18:J20 ASC_Address = B8:J8 And so on Regards Andrew "Ron de Bruin" wrote: Are they all one cell the named ranges ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Hi Andrew
I try to create a example tomorrow evening for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron Except for a few single cells, they are all merged cells, for example: ASC_Account = B9:C9 Fault = B18:J20 ASC_Address = B8:J8 And so on Regards Andrew "Ron de Bruin" wrote: Are they all one cell the named ranges ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Sorry a bit late
Try this one that I made fast so look out for errors<g Change MyPath = "C:\Users\Ron\test" And add the names in this line Addr = Array("name1", "name2", "name3", "name4") Sub MergeAllWorkbooks() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long Dim Addr As Variant Dim N As Long Dim colnum As Long 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder FNum = 0 Do While FilesInPath < "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 'Loop through all files in the array(myFiles) If FNum 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then BaseWks.Cells(rnum, "A").Value = MyFiles(FNum) Addr = Array("name1", "name2", "name3", "name4") colnum = 2 For N = LBound(Addr) To UBound(Addr) On Error Resume Next Set sourceRange = Nothing With mybook.Worksheets(1) Set sourceRange = .Range(Addr(N)) End With Set destrange = BaseWks.Cells(rnum, colnum) destrange.Value = sourceRange.Cells(1).Value On Error GoTo 0 colnum = colnum + 1 Next N rnum = rnum + 1 mybook.Close savechanges:=False End If Next FNum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Andrew I try to create a example tomorrow evening for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron Except for a few single cells, they are all merged cells, for example: ASC_Account = B9:C9 Fault = B18:J20 ASC_Address = B8:J8 And so on Regards Andrew "Ron de Bruin" wrote: Are they all one cell the named ranges ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Excelent! This is working great - thanks a lot!!!
Just to puch my luck a bit...... I have a "Nice to have" request: Is it possible to have a header row generated by the named ranges? Thanks again! Andrew "Ron de Bruin" wrote: Sorry a bit late Try this one that I made fast so look out for errors<g Change MyPath = "C:\Users\Ron\test" And add the names in this line Addr = Array("name1", "name2", "name3", "name4") Sub MergeAllWorkbooks() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long Dim Addr As Variant Dim N As Long Dim colnum As Long 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder FNum = 0 Do While FilesInPath < "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 'Loop through all files in the array(myFiles) If FNum 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then BaseWks.Cells(rnum, "A").Value = MyFiles(FNum) Addr = Array("name1", "name2", "name3", "name4") colnum = 2 For N = LBound(Addr) To UBound(Addr) On Error Resume Next Set sourceRange = Nothing With mybook.Worksheets(1) Set sourceRange = .Range(Addr(N)) End With Set destrange = BaseWks.Cells(rnum, colnum) destrange.Value = sourceRange.Cells(1).Value On Error GoTo 0 colnum = colnum + 1 Next N rnum = rnum + 1 mybook.Close savechanges:=False End If Next FNum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Andrew I try to create a example tomorrow evening for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron Except for a few single cells, they are all merged cells, for example: ASC_Account = B9:C9 Fault = B18:J20 ASC_Address = B8:J8 And so on Regards Andrew "Ron de Bruin" wrote: Are they all one cell the named ranges ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Try this one Andrew
Sub MergeAllWorkbooks() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long Dim Addr As Variant Dim N As Long Dim colnum As Long 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder FNum = 0 Do While FilesInPath < "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 2 Addr = Array("name1", "name2", "name3", "name4") BaseWks.Cells(1, 2).Resize(, UBound(Addr) + 1).Value = Addr 'Loop through all files in the array(myFiles) If FNum 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then BaseWks.Cells(rnum, "A").Value = MyFiles(FNum) colnum = 2 For N = LBound(Addr) To UBound(Addr) On Error Resume Next Set sourceRange = Nothing With mybook.Worksheets(1) Set sourceRange = .Range(Addr(N)) End With Set destrange = BaseWks.Cells(rnum, colnum) destrange.Value = sourceRange.Cells(1).Value On Error GoTo 0 colnum = colnum + 1 Next N rnum = rnum + 1 mybook.Close savechanges:=False End If Next FNum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Excelent! This is working great - thanks a lot!!! Just to puch my luck a bit...... I have a "Nice to have" request: Is it possible to have a header row generated by the named ranges? Thanks again! Andrew "Ron de Bruin" wrote: Sorry a bit late Try this one that I made fast so look out for errors<g Change MyPath = "C:\Users\Ron\test" And add the names in this line Addr = Array("name1", "name2", "name3", "name4") Sub MergeAllWorkbooks() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long Dim Addr As Variant Dim N As Long Dim colnum As Long 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder FNum = 0 Do While FilesInPath < "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 'Loop through all files in the array(myFiles) If FNum 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then BaseWks.Cells(rnum, "A").Value = MyFiles(FNum) Addr = Array("name1", "name2", "name3", "name4") colnum = 2 For N = LBound(Addr) To UBound(Addr) On Error Resume Next Set sourceRange = Nothing With mybook.Worksheets(1) Set sourceRange = .Range(Addr(N)) End With Set destrange = BaseWks.Cells(rnum, colnum) destrange.Value = sourceRange.Cells(1).Value On Error GoTo 0 colnum = colnum + 1 Next N rnum = rnum + 1 mybook.Close savechanges:=False End If Next FNum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Andrew I try to create a example tomorrow evening for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron Except for a few single cells, they are all merged cells, for example: ASC_Account = B9:C9 Fault = B18:J20 ASC_Address = B8:J8 And so on Regards Andrew "Ron de Bruin" wrote: Are they all one cell the named ranges ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew Do you want to merge worksheets or workbooks ? Does have each sheet/workbook the same named ranges ? Give more info so I or anybody else can help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi All Im strugling to get the merge code to work with multiple named ranges. I want to collate info from lots of forms. The forms have named ranges in them, which are scattered throughout the form. Ive tried loads of different ways of reffering to the ranges, but just cant make it work. Original pieces of code: SourceRng:="A5:D5", _ also from another file, the piece of code is Set sourceRange = .Range("A1:C1") Any help would be greatly appreciated! Thanks Andrew |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ron de Bruin's Merge - Can I use multiple named ranges??
Top stuff!! Thanks a heap - this is so useful!
Met vriendelijke groeten Andrew "Ron de Bruin" wrote: Try this one Andrew Sub MergeAllWorkbooks() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long Dim Addr As Variant Dim N As Long Dim colnum As Long 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder FNum = 0 Do While FilesInPath < "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 2 Addr = Array("name1", "name2", "name3", "name4") BaseWks.Cells(1, 2).Resize(, UBound(Addr) + 1).Value = Addr 'Loop through all files in the array(myFiles) If FNum 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then BaseWks.Cells(rnum, "A").Value = MyFiles(FNum) colnum = 2 For N = LBound(Addr) To UBound(Addr) On Error Resume Next Set sourceRange = Nothing With mybook.Worksheets(1) Set sourceRange = .Range(Addr(N)) End With Set destrange = BaseWks.Cells(rnum, colnum) destrange.Value = sourceRange.Cells(1).Value On Error GoTo 0 colnum = colnum + 1 Next N rnum = rnum + 1 mybook.Close savechanges:=False End If Next FNum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Excelent! This is working great - thanks a lot!!! Just to puch my luck a bit...... I have a "Nice to have" request: Is it possible to have a header row generated by the named ranges? Thanks again! Andrew "Ron de Bruin" wrote: Sorry a bit late Try this one that I made fast so look out for errors<g Change MyPath = "C:\Users\Ron\test" And add the names in this line Addr = Array("name1", "name2", "name3", "name4") Sub MergeAllWorkbooks() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long Dim Addr As Variant Dim N As Long Dim colnum As Long 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder FNum = 0 Do While FilesInPath < "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 'Loop through all files in the array(myFiles) If FNum 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then BaseWks.Cells(rnum, "A").Value = MyFiles(FNum) Addr = Array("name1", "name2", "name3", "name4") colnum = 2 For N = LBound(Addr) To UBound(Addr) On Error Resume Next Set sourceRange = Nothing With mybook.Worksheets(1) Set sourceRange = .Range(Addr(N)) End With Set destrange = BaseWks.Cells(rnum, colnum) destrange.Value = sourceRange.Cells(1).Value On Error GoTo 0 colnum = colnum + 1 Next N rnum = rnum + 1 mybook.Close savechanges:=False End If Next FNum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Andrew I try to create a example tomorrow evening for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron Except for a few single cells, they are all merged cells, for example: ASC_Account = B9:C9 Fault = B18:J20 ASC_Address = B8:J8 And so on Regards Andrew "Ron de Bruin" wrote: Are they all one cell the named ranges ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Andrew P." wrote in message ... Hi Ron I want to merge worksheets. Each file will have just one worksheet, and the same named ranges, although it is conceivable that at some time in the future, some may removed or added. The idea was to have the macro always refer to the named ranges so that if the form changes, the macro wont need to be modified and will work with new and old files. There are 37 named ranges - eg: ASC_Name =ChangeoverRequest!$B$7 ASC_Phone =ChangeoverRequest!$B$10 Authorised_By =ChangeoverRequest!$C$37 Customer =ChangeoverRequest!$G$13 Customer_Address =ChangeoverRequest!$B$15 Customer_Contact =ChangeoverRequest!$B$14 Customer_Phone =ChangeoverRequest!$G$14 Date_Ordered =ChangeoverRequest!$I$31 Deliver_To =ChangeoverRequest!$C$21 etc.... Thanks a lot! Andrew "Ron de Bruin" wrote: Hi Andrew |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Named Ranges - Multiple Worksheets | Excel Discussion (Misc queries) | |||
| merge named ranges ? | Charts and Charting in Excel | |||
| merge named ranges | Excel Worksheet Functions | |||
| Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
| how to merge ranges from multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
