Adding data in sheets

Kanidru · May 24th 2004

I have a friend who has many Excel files, each containing data in four sheets. She wants to add the data in the sheets together, or even the data in different files, if there's a way for her to do that. We're using Office XP on a Windows 2000 computer, if that makes a difference in getting advice. Any way we can add these together?

yjoshi · May 25th 2004

Hi This can be certainly done.. no probs.

I have worked with lot of these before. If you can give a few more pointers to the exact problems, it will be really great.

such as what is the structure of data, how many columns, how many sheets,
do you want to just dump the data one below the other or something else ... etch.

See following code which have used to consolidate data on one sheet from different sheets in the same file.

Code

Sub consolidate()
Sheets("Complete").Select ' Selects the sheet where consolidation is needed
Range("Complete_Data_Clr").Clear ' clears existing data, you may skip this
    For Each Sheet In Sheets
    
    If Sheet.Name <> "Data Analysis" And Sheet.Name <> "Complete" And Sheet.Name <> "Entry Form" And Sheet.Name <> "Consolidate" Then
    ' Excluding some sheets which need not be consolidated 
       nam = Sheet.Name
       data_nam = "=OFFSET(" & nam & "!R2C1,0,0,COUNTA(" & nam & "!R2C1:R4999C1),8)"
       ActiveWorkbook.Names.Add Name:=nam, RefersToR1C1:=data_nam
       ' creating name for data on that sheet. Works easy with other processing
       Sheets(nam).AutoFilterMode = False 'Remove filters if any existing
       Sheets(nam).Range(nam).Copy (Sheets("Complete").Range("A65536").End(xlUp).Offset(1, 0)) 'copy the named range below the last used cell 
    End If
    Next
End Sub

Display More

Adding data in sheets

Participate now!

Share