Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to consolidate variable ranges in excel vba
can anyone help? I want to be able to write a piece of vba code that will
automatically scan through a series of worksheets, identify the data range, and then consolidate each worksheet data range into one sheet. The problem I have is the 'Consolidate Method' uses an Array for determining the ranges needed to consolidate. I've written the following code.. Dim Report As Worksheet Dim RawData(30) As Range Dim a As Integer a = 1 For Each Report In ThisWorkbook.Worksheets Report.Activate Range("a3").Select RName = Report.Name ' following identifies data range With Application.WorksheetFunction r = 65537 - .CountBlank(Report.Range("A:A")) c = 258 - .CountBlank(Report.Range("2:2")) End With Set RawData(a) = Report.Range(Cells(2, 1), Cells(r, c)) a = a + 1 Next Report Worksheets("Sheet1").Range("B3").Consolidate _ Sources:=Array(RawData(1), RawData(2)..etc), _ Function:=xlSum, LeftColumn:=True, TopRow:=True Many thanks in advance Rich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to consolidate variable ranges in excel vba
Hi Rich,
Hola Noe´s, Assuming that: 1) the first sheet is where you want to consolidate the rest of sheets 2) all sheets have the same data structure (number and order of columns, data types, etc.) 3) data start at row 2 in all sheets ....try the code below Regards, KL Sub Consolidate() Dim ws As Long, wsFinal As Worksheet Dim rngOrig As Range, rngDest As Range Dim r As Long, rLast As Long, cLast As Integer Application.ScreenUpdating = False With ThisWorkbook Set wsFinal = .Worksheets(1) cLast = wsFinal.Columns(256).End(xlToLeft).Column For ws = 2 To .Worksheets.Count r = wsFinal.Range("A65536").End(xlUp).Row With .Worksheets(ws) rLast = .Range("A65536").End(xlUp).Row Set rngOrig = .Range(.Cells(2, 1), _ .Cells(rLast, cLast)) End With With wsFinal Set rngDest = .Range(.Cells(r + 1, 1), _ .Cells(r + rLast - 1, cLast)) End With rngDest.Value = rngOrig.Value Next ws End With Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to consolidate variable ranges in excel vba
Please disregard the second line of my previous message as I accidentally
copied it from another one in Spanish. KL "KL" wrote in message ... Hi Rich, Hola Noe´s, Assuming that: 1) the first sheet is where you want to consolidate the rest of sheets 2) all sheets have the same data structure (number and order of columns, data types, etc.) 3) data start at row 2 in all sheets ...try the code below Regards, KL Sub Consolidate() Dim ws As Long, wsFinal As Worksheet Dim rngOrig As Range, rngDest As Range Dim r As Long, rLast As Long, cLast As Integer Application.ScreenUpdating = False With ThisWorkbook Set wsFinal = .Worksheets(1) cLast = wsFinal.Columns(256).End(xlToLeft).Column For ws = 2 To .Worksheets.Count r = wsFinal.Range("A65536").End(xlUp).Row With .Worksheets(ws) rLast = .Range("A65536").End(xlUp).Row Set rngOrig = .Range(.Cells(2, 1), _ .Cells(rLast, cLast)) End With With wsFinal Set rngDest = .Range(.Cells(r + 1, 1), _ .Cells(r + rLast - 1, cLast)) End With rngDest.Value = rngOrig.Value Next ws End With Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to consolidate variable ranges in excel vba
Thanks for your rapid response..however I don't think what you've written
will help. I've been using the consolidate method as it appears to be the only way to combine the data in each sheet. The problem I have is that the number of rows and headers in each worksheet varies. However, some rows and columns are the same, so the consolidate method pulls all the data into one big table (and sums up any numbers which are appearing in two separate sheets). What I really need to solve my problem is a way of passing the ranges to the Sources:= variable in the Consolidate Method. Is there a way in which I could create a string that reads in the correct format for the Sources:= Array(...) Thanks again Rich "KL" wrote in message ... Please disregard the second line of my previous message as I accidentally copied it from another one in Spanish. KL "KL" wrote in message ... Hi Rich, Hola Noe´s, Assuming that: 1) the first sheet is where you want to consolidate the rest of sheets 2) all sheets have the same data structure (number and order of columns, data types, etc.) 3) data start at row 2 in all sheets ...try the code below Regards, KL Sub Consolidate() Dim ws As Long, wsFinal As Worksheet Dim rngOrig As Range, rngDest As Range Dim r As Long, rLast As Long, cLast As Integer Application.ScreenUpdating = False With ThisWorkbook Set wsFinal = .Worksheets(1) cLast = wsFinal.Columns(256).End(xlToLeft).Column For ws = 2 To .Worksheets.Count r = wsFinal.Range("A65536").End(xlUp).Row With .Worksheets(ws) rLast = .Range("A65536").End(xlUp).Row Set rngOrig = .Range(.Cells(2, 1), _ .Cells(rLast, cLast)) End With With wsFinal Set rngDest = .Range(.Cells(r + 1, 1), _ .Cells(r + rLast - 1, cLast)) End With rngDest.Value = rngOrig.Value Next ws End With Application.ScreenUpdating = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to consolidate variable ranges in excel vba
Rich,
Comments and clarification... The source array must be a string and include the full address using R1C1 notation. The sheet to receive the consolidated data must not overlap any of the data being consolidated. Variables r and c should be declared. Using countblank as you have won't always give you the last cell. See if the following code makes sense and works for you. Regards, Jim Cone San Francisco, USA '---------------------------------- Sub NewsPostTest() Dim Report As Excel.Worksheet Dim RawData() As String Dim a As Long Dim r As Long Dim c As Long ReDim RawData(1 To Worksheets.Count) a = 1 'For Each Report In ThisWorkbook.Worksheets For Each Report In ActiveWorkbook.Worksheets Report.Activate Range("a3").Select 'following identifies data range r = Report.Cells(Rows.Count, 1).End(xlUp).Row c = Report.Cells(2, Columns.Count).End(xlToLeft).Column RawData(a) = Report.Range(Cells(2, 1), Cells(r, c)) _ .Address(ReferenceStyle:=xlR1C1, External:=True) a = a + 1 Next 'Report Worksheets.Add befo=Worksheets(1) ActiveSheet.Range("B3").Consolidate Sources:=Array(RawData()), _ Function:=xlSum, LeftColumn:=True, TopRow:=True Set Report = Nothing End Sub '------------------------------- "Rich" wrote in message ... can anyone help? I want to be able to write a piece of vba code that will automatically scan through a series of worksheets, identify the data range, and then consolidate each worksheet data range into one sheet. The problem I have is the 'Consolidate Method' uses an Array for determining the ranges needed to consolidate. I've written the following code.. Dim Report As Worksheet Dim RawData(30) As Range Dim a As Integer a = 1 For Each Report In ThisWorkbook.Worksheets Report.Activate Range("a3").Select RName = Report.Name ' following identifies data range With Application.WorksheetFunction r = 65537 - .CountBlank(Report.Range("A:A")) c = 258 - .CountBlank(Report.Range("2:2")) End With Set RawData(a) = Report.Range(Cells(2, 1), Cells(r, c)) a = a + 1 Next Report Worksheets("Sheet1").Range("B3").Consolidate _ Sources:=Array(RawData(1), RawData(2)..etc), _ Function:=xlSum, LeftColumn:=True, TopRow:=True Many thanks in advance Rich |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to consolidate variable ranges in excel vba
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/examples.htm The "Bread-Roll" consolidation method - great for accountants. See how simple it is to consolidate any combination of your organisation's accounts. (No VBA used) -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Rich" wrote in message ... can anyone help? I want to be able to write a piece of vba code that will automatically scan through a series of worksheets, identify the data range, and then consolidate each worksheet data range into one sheet. The problem I have is the 'Consolidate Method' uses an Array for determining the ranges needed to consolidate. I've written the following code.. Dim Report As Worksheet Dim RawData(30) As Range Dim a As Integer a = 1 For Each Report In ThisWorkbook.Worksheets Report.Activate Range("a3").Select RName = Report.Name ' following identifies data range With Application.WorksheetFunction r = 65537 - .CountBlank(Report.Range("A:A")) c = 258 - .CountBlank(Report.Range("2:2")) End With Set RawData(a) = Report.Range(Cells(2, 1), Cells(r, c)) a = a + 1 Next Report Worksheets("Sheet1").Range("B3").Consolidate _ Sources:=Array(RawData(1), RawData(2)..etc), _ Function:=xlSum, LeftColumn:=True, TopRow:=True Many thanks in advance Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate tables/ranges with text from multiple worksheets | Excel Discussion (Misc queries) | |||
VBA variable definition help needed. | New Users to Excel | |||
Help with Variable Ranges! | Excel Discussion (Misc queries) | |||
How do I consolidate data into ranges | Excel Discussion (Misc queries) | |||
Variable ranges | Excel Worksheet Functions |